# coding: utf-8

import os
import time
import math
import urllib
import json
from datetime import date, datetime, timedelta
from decimal import Decimal
from collections import namedtuple, defaultdict, OrderedDict

from flask import current_app
from flask_login import current_user
from sqlalchemy.sql import text, func

from app.wechat.constant import WxUserApprovalStatus
from ..forms import NotChoices
from common.bid_notify.sql_statement import Statement
from common.bid_notify.message import MessageNotify
from common.constant import BidQueueStatus, BID_USER_COUNT, TAIWAN_CODE, \
    DEMAND_NOTIFY_LIMIT, VoucherStatus, GiveVoucherReason, \
    RedeemStatus, RedeemType, RedeemGiftType
from common.schedule.demand_utils import bind_virtual_number
from common.constant import (
    PARTIC_CACHE_KEY_PREFIX, MEMBERS_PHONE
)
from common.utils import redis_client
from ..extensions import db
from ..models import (
    AbstractModel, BaseModel, ExtensionalModel,
    get_timestamp, get_timestamp_ms
)
from ..constant import Choice, Gender
from .constant import (
    DemandCate, DemandMalf, DemandType, DemandSurr, DemandColor,
    BidStatusDisplay, BillChnl, MAX_AREA_DISPLAY, UserType, UserActive,
    DemandStatus, DemandRentType, DemandFinishImgState, WxInviteState,
    AreaType, DemandTag, ParticStatus, BillSubType, BillType, BillStatus,
    SmsMarketingLevel, IncrementPackType, BidCostType,
    UserStatus, UserLevel, FollowType, SEMGroup,
    DemandClientSource, OperateResult, UserTrainingStatus,
    OperatedStatus, FeedbackStatus, MONTHLY_REFUSE_MAX,
    MarketingStatus, UserVisitDemandChannel,
    TRIAL_BID_POINT, GIFT_PACK_USER_SET,
    HAD_POPUP_PROMPT_SET, GameRewardStatus, ApplyRealNumberState,
    TRIAL_VOUCHER_BID_POINT, TRIAL_VOUCHER_DAYS, TRIAL_VOUCHER_CNT,
)
from common.models import Feedback, EnterpriseLevel

# 工程商查看过项目的记录
supplier_demand_table = db.Table(
    'supplier_demand',
    db.Column('demand_id', db.Integer, db.ForeignKey('demand.id'), primary_key=True),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True)
)


# 工程商业务范围城市
user_to_area = db.Table(
    'user_to_area',
    db.Column('area_id', db.Integer, db.ForeignKey('area.id'), primary_key=True),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True)
)


class Area(BaseModel):
    """
    省，市，县/区
    """
    __tablename__ = 'area'

    type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    name = db.Column(
        db.String(32, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default='',
    )
    pid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    war_zone_id = db.Column(db.ForeignKey('war_zone.id'))
    level = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    water_factor = db.Column(
        db.Numeric(10, 2), nullable=False, server_default=text("'1.0'"), default=1.0)

    def __repr__(self):
        """
        created by chenhj at 2017/8/11

        """
        return self.name

    @property
    def cities(self):
        return Area.query.filter_by(pid=self.id)

    @classmethod
    def generate_fake(cls, count=5):
        """
        created by chenhj at 2017/8/11

        """
        import forgery_py
        for i in range(count):
            cls.create(
                type=1,
                name=forgery_py.address.city(),
            )
        db.session.commit()

    @classmethod
    def get_area_id(cls, prov, city=None):
        """
        created by chenhj at 2017/8/15

        """
        if city:
            area = Area.query.filter_by(name=city, type=AreaType.CITY.value).first()
            if area:
                return area.id
        else:
            area = Area.query.filter_by(name=prov).first()
            if area:
                return area.id
        return None

    @classmethod
    def get_city_ids(cls, prov_name):
        """
        获取同省份的城市id列表
        :return:
        """
        prov = cls.get_by_args(name=prov_name, type=AreaType.PROV.value)
        city_ids = list()
        if prov:
            cities = cls.query.filter(cls.pid == prov.id).with_entities(cls.id)
            city_ids = [each.id for each in cities]
        return city_ids

    @classmethod
    def make_prov_city_tree(cls, active_city_ids=None):
        """
        add by zhouhl 2018-05-08
        构造省-市的json数据，供zTree插件渲染使用
        """
        result = []
        active_city_ids = active_city_ids or []

        # 省加入到result
        all_provs = Area.query.filter_by(
            type=AreaType.PROV.value,
        ).order_by(
            Area.id.asc()
        )
        for prov in all_provs:
            result.append({
                'id': str(prov.id),
                'pId': 0,
                'name': prov.name,
                'checked': False,
                'open': False,
            })

        # 城市加入到result
        all_cities = Area.query.filter_by(
            type=AreaType.CITY.value,
        )
        for city in all_cities:
            result.append({
                'id': str(city.id),
                'pId': str(city.pid),
                'name': city.name,
                'checked': True if city.id in active_city_ids else False,
                'open': False,
            })

        return result


class Audio(BaseModel):
    """
    电话录音表
    """
    __tablename__ = 'audio'

    uid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    mobile = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        index=True,
        server_default=text("''"),
        default='',
    )
    audio = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default='',
    )
    record_date = db.Column(
        db.Date, nullable=False, server_default=text("'1000-01-01'"))
    record_datetime = db.Column(db.DateTime(), nullable=True)
    # 七陌的call_id
    seven_moor_call_id = db.Column(db.String(256), server_default=text("''"), default='')

    owner = db.relationship(
        "User",
        #primaryjoin="foreign(Audio.uid) == remote(User.id)",
        primaryjoin="foreign(Audio.mobile) == remote(User.mobile)",
        single_parent=True,
        backref=db.backref('audios', lazy='dynamic'),
    )

    @property
    def audio_url(self):
        if self.audio:
            host = os.environ.get('audio_host')
            return urllib.parse.urljoin(host, self.audio)
        return self.audio


class Bill(BaseModel):
    """
    交易记录表
    """
    __tablename__ = 'bill'

    filter_dict = {
        'id': (),
        'uid': (),
        'item': (),
        'type': (),
        'sub_type': (),
        'status': (),
    }

    uid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    chnl = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    chnl_code = db.Column(
        db.String(32, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    sub_type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    item = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    amt = db.Column(
        db.Numeric(16, 2),
        nullable=False,
        server_default=text("'0.00'"),
        default=Decimal('0.00')
    )
    call_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 汇币(取代原有的抢单次数)
    bid_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)

    sms_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    desc = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_name = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_acct_name = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_acct_num = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    status = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)

    owner = db.relationship(
        'User',
        primaryjoin='and_(foreign(Bill.uid) == remote(User.id),'
                    'Bill.deleted_at == 0)',
        single_parent=True,
        backref=db.backref('bills', lazy='dynamic'),
    )

    @property
    def chnl_display(self):
        return BillChnl(self.chnl).display

    @property
    def type_display(self):
        return BillType(self.type).display

    @property
    def subtype_display(self):
        return BillSubType(self.sub_type).display

    @property
    def status_display(self):
        return BillStatus(self.status).display


class Comment(BaseModel):
    """
    项目评论表，类似于客服的跟进记录
    """
    __tablename__ = 'cmt'

    demand_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    uid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    content = db.Column(
        db.String(1024, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )

    # 项目的跟进记录
    demand = db.relationship(
        'Demand',
        primaryjoin='and_(foreign(Comment.demand_id) == remote(Demand.id),'
            'Comment.deleted_at==0)',
        single_parent=True,
        backref=db.backref('comments', lazy='dynamic'),
    )

    # 评论人
    owner = db.relationship(
        'User',
        primaryjoin='and_(foreign(Comment.uid) == remote(User.id),'
            'Comment.deleted_at==0)',
        single_parent=True,
        backref=db.backref('comments', lazy='dynamic'),
    )


class Demand(BaseModel):
    """
    项目表，也可以叫需求表
    """
    __tablename__ = 'demand'

    search_keys = ('admin_remark', 'sem_plan', 'sem_unit', 'sem_keyword', 'remark')

    filter_dict = {
        'id': (),
        'uid': (),
        'area_id': (),
        'prov': (),
        'cate': (),
        'malf': (),
        'type': (),
        'surr': (),
        'color': (),
        'status': (),
        'tag': (),
        'source': (),
        'admin_id': (),
        'created_at_begin': ('created_at', '>='),
        'created_at_end': ('created_at', '<='),
        'published_at_begin': ('published_at', '>='),
        'published_at_end': ('published_at', '<='),
        'sem_source': (),
        'sem_phone_number': (),
    }

    order_keys = ('created_at', 'updated_at', 'published_at')

    # 默认的排序字段
    default_order_key = 'created_at'

    default_order_asc = False

    uid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    area_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    cate = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    malf = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    surr = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    size = db.Column(
        db.Numeric(16, 2),
        nullable=False,
        server_default=text("'0.00'"),
        default=Decimal('0.00')
    )
    color = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    span = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    prov = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    city = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    addr = db.Column(
        db.String(128, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    img = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    audio = db.Column(
        db.String(256, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    audio_sensitive = db.Column(
        db.Boolean, nullable=False, server_default=text('FALSE'), default=False
    )
    budget = db.Column(
        db.Numeric(16, 2),
        nullable=False,
        server_default=text("'0.00'"),
        default=Decimal('0.00')
    )
    im_group_id_201704 = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    bid_cnt = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    vote_up = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    vote_down = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    ip = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    device_cookies = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    source = db.Column(db.String(128), nullable=False, default='')
    sem = db.Column(db.JSON)
    tag = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    status = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    remark = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    admin_remark = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    # 发布备注, dict保存
    publish_remark = db.Column(db.Text, default='{}')
    # 发布者ID
    admin_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 发布时间
    published_at = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 发布次数
    published_count = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 浏览人数
    scan_count = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 是否派单
    is_dispatch = db.Column(db.Boolean(), default=False, server_default=text('false'))
    # 派单人数
    dispatch_count = db.Column(db.Integer, default=0, server_default=text("'0'"))

    # 搜索引擎相关
    sem_source = db.Column(db.String(32), default='')
    sem_plan = db.Column(db.String(256), default='')
    sem_keyword = db.Column(db.String(256), default='')
    sem_unit = db.Column(db.String(256), default='')
    sem_e_matchtype = db.Column(db.String(256), default='')
    sem_e_creative = db.Column(db.String(256), default='')
    sem_e_adposition = db.Column(db.String(256), default='')
    sem_e_pagenum = db.Column(db.String(256), default='')
    sem_e_keywordid = db.Column(db.String(256), default='')

    # 竞价起拍点
    bid_start_point = db.Column(
        db.Integer(), default=10, server_default=text("'0'"))

    # 是否金牌项目
    for_gold = db.Column(
        db.Boolean(), default=False, server_default=text('false')
    )
    # 被克隆为金牌订单的次数
    cloned_count = db.Column(
        db.Integer(), default=0, server_default=text("'0'"))

    # add by zhouhl 2018-01-04 记录发布项目时，该客户是通过哪组SEM推广的号码进来的
    sem_phone_number = db.Column(db.String(32), default='', server_default=text("''"))
    # 七陌的call_id
    seven_moor_call_id = db.Column(db.String(256), server_default=text("''"), default='')
    # 公司id, 标识这个项目是来自于该公司品牌官网的留电
    enterprise_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 订单分配数量
    notify_limit = db.Column(
        db.Integer, default=DEMAND_NOTIFY_LIMIT, server_default=text("'3'")
    )

    # NOTE 缺少外键约束的写法
    owner = db.relationship(
        'User',
        primaryjoin='and_(foreign(Demand.uid) == remote(User.id),'
            'Demand.deleted_at==0)',
        single_parent=True,
        backref=db.backref('demands', lazy='dynamic'),
    )
    admin = db.relationship(
        'AdminUser',
        primaryjoin='foreign(Demand.admin_id) == remote(AdminUser.id)',
        single_parent=True,
    )

    area = db.relationship(
        'Area',
        primaryjoin='foreign(Demand.area_id) == remote(Area.id)',
        single_parent=True,
    )

    def __init__(self, *args, **kwargs):
        """
        created by chenhj at 2017/9/13

        """
        super(Demand, self).__init__(*args, **kwargs)
        if self.sem:
            try:
                sem = json.loads(self.sem)
                self.sem_source = sem.get('source', '')
                self.sem_plan = sem.get('plan', '')
                self.sem_keyword = sem.get('keyword', '')
                self.sem_unit = sem.get('unit', '')
                self.sem_e_matchtype = sem.get('e_matchtype', '')
                self.sem_e_creative = sem.get('e_creative', '')
                self.sem_e_adposition = sem.get('e_adposition', '')
                self.sem_e_pagenum = sem.get('e_pagenum', '')
                self.sem_e_keywordid = sem.get('e_keywordid', '')
            except:
                pass

    @classmethod
    def get_last_draft_demand(cls, uid):
        """
        add by zhouhl 2018-01-19
        返回指定用户最后提交的草稿或者待审核项目
        """
        return cls.query.filter(
            cls.uid == uid,
            cls.status.in_([
                DemandStatus.DRAFT.value,
                DemandStatus.UNCHECKED.value
            ])
        ).order_by(
            cls.id.desc()
        ).first()

    @property
    def cate_display(self):
        return DemandCate(self.cate).display

    @property
    def malf_display(self):
        return DemandMalf(self.malf).display

    @property
    def type_display(self):
        # 安装/维修时的类型
        if self.cate in (DemandCate.INSTALL.value, DemandCate.MAINTAIN.value):
            return DemandType(self.type).display
        # 租赁时的类型
        if self.cate == DemandCate.RENT.value:
            return DemandRentType(self.type).display
        return ''

    @property
    def is_ongoing(self):
        """
        create_by ld_wu 2017-10-16
        项目是否在进行中
        """
        return self.status == DemandStatus.ONGOING.value

    @property
    def is_install(self):
        return self.cate == DemandCate.INSTALL.value

    @property
    def surr_display(self):
        return DemandSurr(self.surr).display

    @property
    def color_display(self):
        return DemandColor(self.color).display

    @property
    def tag_display(self):
        return DemandTag(self.tag).display

    @property
    def status_display(self):
        return DemandStatus(self.status).display

    @property
    def admin_display(self):
        return self.admin.nickname if self.admin else '无'

    @property
    def img_url(self):
        if self.img:
            host = os.environ.get('img_host')
            return urllib.parse.urljoin(host, self.img)
        return self.img

    @property
    def audio_url(self):
        if self.audio:
            host = os.environ.get('audio_host')
            return urllib.parse.urljoin(host, self.audio)
        return self.audio

    @property
    def published_datetime(self):
        return datetime.fromtimestamp(self.published_at) \
            if self.published_at else ''

    @property
    def last_admin_remark(self):
        return self.admin_remark.split('\n')[0]

    @property
    def max_partic_offer(self):
        """
        created by chenhj at 2017/10/30
        取竞价模式时该项目的最大竞价点
        """
        timestamp = get_timestamp()
        return db.session.query(
            db.func.max(Partic.offer)
        ).filter(
            Partic.demand_id == self.id,
            Partic.published_count == self.published_count,
            Partic.demand_status == DemandStatus.ONGOING.value,
            Partic.offer_at <= timestamp
        ).scalar() or 0

    @property
    def is_bid_status(self):
        """
        create by ld_wu 2017-12-05
        当前项目是否在中标流程中，即有人中标以后未联系或汇币不足(排除赠单的)
        :return:
        """
        return Partic.query.filter(
            Partic.status.in_(
                [
                    ParticStatus.NOT_CONTACT.value,
                    ParticStatus.BALANCE_NOT_ENOUGH.value
                ]
            ),
            Partic.published_count == self.published_count,
            Partic.demand_id == self.id,
            Partic.deleted_at == 0,
            Partic.is_training.is_(False)
        ).first()

    @property
    def is_locked(self):
        """
        项目是否被独家锁定
        :return:
        """
        return Partic.query.filter(
            Partic.status == ParticStatus.SUCCEED.value,
            Partic.demand_id == self.id,
            Partic.deleted_at == 0,
            Partic.follow_type == FollowType.LOCKED.value
        ).first()

    @property
    def demand_client_obj(self):
        return DemandClient.get_by_args(demand_id=self.id)

    @property
    def partic_success_cnt(self):
        """
        项目当前有效联系人数
        :return:
        """
        return Partic.query.filter(
            Partic.demand_id == self.id,
            Partic.is_robot.is_(False),
            Partic.status == ParticStatus.SUCCEED.value,
            Partic.follow_type != FollowType.GIVE_UP.value
        ).count()

    @classmethod
    def get_today_active_demands_count_by_area(cls, area_ids):
        """
        created by chenhj at 2017/8/14
        取area范围的进行中/已结束的项目数
        """
        today = date.today()
        start_date = datetime(today.year, today.month, today.day)
        start = start_date.strftime('%s')
        end = time.time()
        return cls.query.filter(
            cls.published_at >= start,
            cls.published_at <= end,
            cls.area_id.in_(area_ids),
            cls.status.notin_([
                DemandStatus.DRAFT.value,
                DemandStatus.UNCHECKED.value,
                DemandStatus.INVALID.value,
                DemandStatus.DISPATCHING.value,
                DemandStatus.MASTER_DISPATCHING.value
            ])
        ).count()

    def handle_complete(self, operator_id, operate_result, user_id=None, abandon_text=''):
        """
        created by chenhj at 22/01/2018
        同时关闭两个表的数据
        """
        from app.seven_moor.models import SevenMoorRecord
        if not user_id:
            DemandClient.complete(self.uid, operator_id, operate_result, self.id)
            SevenMoorRecord.complete(
                self.uid,
                operate_result,
                demand_id=self.id,
                not_create_reason=abandon_text
            )
        else:
            # 帮别人发布
            DemandClient.complete(user_id, operator_id, operate_result, self.id)
            SevenMoorRecord.complete(
                user_id,
                operate_result,
                demand_id=self.id,
                not_create_reason=abandon_text
            )

    def is_recovery(self, partic_id, published_count=1):
        """
        create by ld_wu 2018-04-03
        是否符合回收条件
        1、首次发布
        2、没有真实工程商中标（未退款）
        :return:
        """

        # 只有完成的项目才会进入（避免项目已经重新发布了）
        if self.status not in (
                DemandStatus.COMPETE_DONE.value,
                DemandStatus.DONE.value):
            return False
        # 维修订单不进入回收站
        if self.cate == DemandCate.MAINTAIN.value:
            return False
        if self.published_count == 1:
            # 订单已锁定则不进入回收站
            if self.is_locked:
                return False

            bid_user_count = Partic.query.filter(
                Partic.demand_id == self.id,
                Partic.status.in_(
                    [
                        ParticStatus.SUCCEED.value,
                        ParticStatus.NOT_CONTACT.value,
                        ParticStatus.BALANCE_NOT_ENOUGH.value
                    ]
                ),
                Partic.follow_type != FollowType.GIVE_UP.value,
                Partic.is_robot.__eq__(False),
                Partic.is_training.__eq__(False),
                Partic.id != partic_id
            ).count()

            if bid_user_count >= BID_USER_COUNT:
                return False
            else:
                return True
        return False

    def recovery_demand(self, partic_id, published_count=1):
        """
        create by ld_wu 2018-04-03
        触发时机为（竞价模式下第二个中标人为机器人或者有人退款审核通过时）
        :return:
        """
        # 判定是否符合回收条件
        # 写入项目回收站
        if self.is_recovery(partic_id, published_count):
            renewable_demand = RenewableDemand.get_by_args(demand_id=self.id)
            if not renewable_demand:
                # 写入订单回收统计
                current_app.common_producer.send(
                    json.dumps(
                        dict(
                            backend='renewable_stat',
                            func='renewable_stat',
                            kwargs=dict(
                                demand_id=self.id
                            )
                        )
                    )
                )
                RenewableDemand.create(
                    demand_id=self.id
                )

    def publish_pre_deal(self):
        """
        created by chenhj at 2018/4/16
        发布项目前的预处理
        """
        # 记录操作者，记录首次发布时间，发布次数加1
        if not self.admin_id:
            self.admin_id = current_user.id
        self.published_at = get_timestamp()

        # 二次发布
        if self.published_count > 0:
            renewable_demand = RenewableDemand.get_by_args(
                demand_id=self.id
            )
            if renewable_demand:

                # 修改机器人为放弃跟进
                Partic.query.filter(
                    Partic.demand_id == self.id,
                    Partic.published_count == self.published_count,
                    Partic.is_robot.is_(True),
                    Partic.status == ParticStatus.SUCCEED.value
                ).update(
                    dict(
                        follow_type=FollowType.GIVE_UP.value
                    ),
                    synchronize_session='fetch'
                )
                if renewable_demand.operated_status == OperatedStatus.NA.value:
                    # 二次发布以后修改处理结果
                    renewable_demand.operated_status = OperatedStatus.REPUBLISH.value
                    renewable_demand.operator_id = current_user.id
                    renewable_demand.operator_name = current_user.nickname

        self.published_count += 1

        # 项目进入发布状态(中间态, 防止还没进入mq处理时此草稿项目被处理了)
        self.status = DemandStatus.PUBLISHING.value

        # 发短信通知客户
        if (
            self.tag == DemandTag.NOT_VIRT.value
            and self.published_count == 1
        ):
            content_template = """您的LED屏需求已发布！需求信息：{span}、{surr}、{color}屏{size}。5分钟内将有2~3家供应商与您联系，请注意接听来电。"""
            content = content_template.format(
                span=self.span,
                surr=self.surr_display,
                color=self.color_display,
                size='、面积约{}㎡'.format(self.size) if self.size else ''
            )
            if self.owner.mobile \
                    and current_app.sms_producer \
                    and not self.owner.is_virt:
                current_app.wph_sms_producer.send(
                    json.dumps({
                        'mobile': self.owner.mobile,
                        'content': content,
                    })
                )

        # 战区统计(台湾的单不统计)
        if int(self.area_id) != TAIWAN_CODE:
            stat_data = None
            demand_area = Area.get_by_id(str(self.area_id)[:2] + '0000')
            if self.published_count == 1:
                stat_data = {
                    'ro_cnt': 1,
                    'published_count': 1,
                    'war_zone_id': demand_area.war_zone_id
                }
            elif self.published_count > 1:
                stat_data = {
                    'published_count': 1,
                    'war_zone_id': demand_area.war_zone_id
                }
            if stat_data:
                current_app.zone_stat_exchanger.exchange_send(json.dumps(stat_data))

    def demand_partics_info(self, user=None):
        """
        create by ld_wu 2018-07-10
        获取该项目抢单信息
        :return:
        """
        partics = self.partics.all()
        dispatch_success_ids = list()
        success_ids = set()
        my_partic = None
        level_6_partic = None
        info = dict(
            dispatch_success_ids=dispatch_success_ids,
            has_demand_locked=False,
            punish_bid_point=0,
            feedback_info=dict(
                feedback=0,
                feedback_display='',
                feedback_status=0,
                is_revert=False,
                feedback_refuse_content=''
            ),
            supplier_partic_status=0,
            partic_timeout=False,
            is_training=False,
            my_offer=0,
            bid_win_at=0,
            partic_demand_status=0,
            my_follow_type=0,
            my_contact_state=0,
            my_contact_seconds=0,
            apply_real_number_state=ApplyRealNumberState.NA.value,
        )
        dispatch_status = (
            DemandStatus.MASTER_DISPATCHING.value,
            DemandStatus.DISPATCHING.value
        )
        # 是否锁定
        for partic in partics:
            if partic.status == ParticStatus.SUCCEED.value:
                # 是否独家锁定
                if partic.follow_type == FollowType.LOCKED.value:
                    info['has_demand_locked'] = True
                if partic.demand_status in dispatch_status:
                    success_ids.add(partic.supplier_id)
                    if partic.enterprise_level == EnterpriseLevel.LEVEL_6.value:
                        if user:
                            # 地方合作伙伴是否已有人抢
                            if user.id != partic.supplier_id:
                                level_6_partic = partic

            # 我的参与状态
            if user and user.type == UserType.ENGINEER.value:
                if partic.supplier_id == user.id:
                    # 只有抢单成功的单才有反馈信息, 这样可以保证多次发布时只有一条反馈信息
                    info['feedback_info'] = dict(
                        feedback=partic.feedback,
                        feedback_display=Feedback(partic.feedback).display,
                        feedback_status=partic.feedback_status,
                        is_revert=partic.is_revert,
                        feedback_refuse_content=partic.feedback_refuse_content
                    )
                    info['punish_bid_point'] = partic.punish_bid_point
                    # 本轮已参加
                    attend_current = (
                        partic.status != ParticStatus.TALKED.value and
                        partic.published_count == self.published_count
                    )
                    # 参与成功或者超时未联系
                    success_or_time_out = (
                        partic.status in (
                            ParticStatus.SUCCEED.value,
                            ParticStatus.TIMEOUT_NOT_CONTACT.value
                        )
                    )

                    # 派单或主人派单
                    is_dispatch = partic.demand_status in dispatch_status

                    if attend_current or success_or_time_out or is_dispatch:
                        info['supplier_partic_status'] = partic.status
                        info['partic_timeout'] = partic.partic_timeout
                        info['my_offer'] = float(partic.offer)
                        info['my_follow_type'] = (
                            partic.follow_type
                            if partic.status == ParticStatus.SUCCEED.value
                            else 0
                        )
                        info['my_contact_state'] = partic.contact_state
                        info['my_contact_seconds'] = partic.contact_seconds
                        info['bid_win_at'] = partic.bid_win_at
                        info['partic_demand_status'] = partic.demand_status
                        info['is_training'] = partic.is_training
                        info['apply_real_number_state'] = partic.apply_real_number_state
                        my_partic = partic
        if success_ids:
            if user:
                if user.id not in success_ids:
                    if my_partic:
                        if my_partic.enterprise_level == EnterpriseLevel.LEVEL_6.value:
                            if level_6_partic:
                                dispatch_success_ids.append(
                                    level_6_partic.supplier_id
                                )
                        else:
                            # 金银牌同企业登记下是否有人抢单成功
                            enterprise = user.enterprises.first()
                            if enterprise:
                                user_ids = {
                                    each.id for each in enterprise.sub_accounts
                                }
                                dispatch_success_ids.extend(list(success_ids & user_ids))
        return info, my_partic


class RenewableDemand(BaseModel):
    """
    create by ld_wu 2018-04-03
    回收站的项目
    """
    filter_dict = {
        'operated_status': ()
    }
    __tablename__ = 'renewable_demands'

    # 项目id
    demand_id = db.Column(db.Integer(), unique=True, nullable=False)
    # 回收时间
    recovery_at = db.Column(db.Integer(), default=get_timestamp, index=True)
    # 回收时间
    recovery_date = db.Column(db.Date, default=datetime.today, index=True)
    # 操作人id
    operator_id = db.Column(db.Integer(), nullable=False, default=0)
    # 操作人姓名
    operator_name = db.Column(db.String(32), nullable=False, default='')
    # 操作时间
    operated_at = db.Column(db.Integer(), default=0, nullable=False)
    # 操作结果
    operated_status = db.Column(db.SMALLINT(), default=0, nullable=False)
    # 抢单成功人数
    recovery_success_count = db.Column(db.SMALLINT(), default=0, nullable=False)

    @property
    def recovery_at_display(self):
        return datetime.fromtimestamp(self.recovery_at)

    @property
    def operated_status_display(self):
        return OperatedStatus(self.operated_status).display

    @property
    def success_count(self):
        """
        create by ld_wu 2018-07-09
        返回抢单成功人数
        :return:
        """
        return Partic.query.filter(
            Partic.demand_id == self.demand_id,
            Partic.status.in_(
                [
                    ParticStatus.SUCCEED.value,
                    ParticStatus.NOT_CONTACT.value,
                    ParticStatus.BALANCE_NOT_ENOUGH.value
                ]
            ),
            Partic.follow_type != FollowType.GIVE_UP.value,
            Partic.is_robot.__eq__(False),
            Partic.is_training.__eq__(False)
        ).count()

    def training_demand(self, mobiles):
        """
        create by ld_wu 2018-04-04
        运营对回收订单进行赠送
        :param mobiles:
        :return:
        """
        error_mobiles = list()
        duplicate_mobiles = list()
        if mobiles:
            users = User.query.filter(
                User.mobile.in_(mobiles),
                User.mobile != '',
            )
            demand = Demand.get_by_id(self.demand_id)
            user_mapping = {each.id: each for each in users}
            existed_mobiles = [each.mobile for each in users]
            # 已经抢过单的人
            partics = Partic.query.filter(
                Partic.supplier_id.in_(user_mapping.keys()),
                Partic.demand_id == self.demand_id
            ).all()
            if len(mobiles) != len(existed_mobiles):
                error_mobiles = [
                    each for each in mobiles if each not in existed_mobiles
                ]

            if partics:
                duplicate_mobiles = {
                    user_mapping[partic.supplier_id].mobile
                    for partic in partics
                }
            if not error_mobiles and not duplicate_mobiles:
                for user in users:
                    current_partic = Partic.create(
                        supplier_id=user.id,
                        status=ParticStatus.NOT_CONTACT.value,
                        demand_id=self.demand_id,
                        is_training=True,
                        published_count=demand.published_count,
                        bid_win_at=get_timestamp()
                    )
                    # 绑定虚拟号
                    bind_virtual_number(
                        db.session,
                        self.demand_id,
                        demand.area_id,
                        demand.owner.mobile,
                        user.mobile
                    )
                    # 写入中标队列
                    BidUserQueue.create(
                        user_id=user.id,
                        status=BidQueueStatus.TRAIN,
                        demand_id=self.demand_id,
                        partic_id=current_partic.id,
                        published_count=demand.published_count,
                        action_id='',
                        mobile=user.mobile
                    )
                    # 发送微信或者app中标通知
                    MessageNotify.send_training_notify(
                        db.session, demand, user
                    )

        return error_mobiles, duplicate_mobiles


class PurchaseDemandInfo(BaseModel):
    """
    add by zhouhl 2017-09-25
    采购项目扩展信息表
    """
    __tablename__ = 'purchase_demand_infos'

    # 对应项目ID
    demand_id = db.Column(db.Integer(), db.ForeignKey('demand.id'))
    # 产品类型ID
    product_type_id = db.Column(db.Integer())
    # 产品ID
    product_id = db.Column(db.Integer(), db.ForeignKey('product_templates.id'))
    # 产品类名称(客户输入)
    product_type_name = db.Column(db.String(128), default='')
    # 批次(客户输入)
    batch_num = db.Column(db.String(128), default='')
    # 型号(客户输入)
    product_model = db.Column(db.String(128), default='')
    # 产品数量
    product_amount = db.Column(
        db.Integer(), default=0, server_default=text("'0'"))
    # 定金
    deposit = db.Column(
        db.Numeric(16, 2),
        nullable=False,
        server_default=text("'0.00'"),
        default=Decimal('0.00')
    )
    # 供货日期，仅年月日
    supply_date = db.Column(db.Date(), nullable=True)
    # 截止时间
    deadline = db.Column(db.DateTime(), nullable=True)

    demand = db.relationship(
        'Demand',
        single_parent=True,
        backref=db.backref('purchase_info', uselist=False),
    )

    product = db.relationship(
        'ProductTemplate',
        backref='purchase_info_list'
    )

    @property
    def product_type_name_display(self):
        from app.products.constant import TypeToName
        return TypeToName.mapping.get(self.product_type_id, '')

    @property
    def product_name_display(self):
        return self.product.name if self.product else ''

    @property
    def is_expired(self):
        """
        是否已过截止期限
        :return:
        """
        if self.deadline:
            return self.deadline <= datetime.now()
        return False


class Pack(BaseModel):
    """
    抢单套餐表
    """
    __tablename__ = 'pack'

    uid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, unique=True)
    # 汇币(取代原来的call_point)
    bid_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    accum_bid_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)

    ### 以下字段均已废弃 begin ###
    area_id = db.Column(db.JSON)
    call_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    sms_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 派单点
    dispatch_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 增加一个记录忠实用户的可用点数，字面上即是付过款的
    paid_sms_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    accum_call_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    accum_sms_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    accum_dispatch_point = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    end_date = db.Column(
        db.Date, nullable=False, server_default=text("'1000-01-01'"))
    # 若sms_end_date不为null, 则表示购买了无限量套餐, 此时不扣他的sms_point
    sms_end_date = db.Column(db.Date)
    # 到期日期(用于方便统计)
    out_date = db.Column(db.Date)
    # 铜牌会费截止日期
    bronze_medal_end_date = db.Column(db.Date)
    ### 以上字段均已废弃 end ###

    # NOTE 缺少外键约束的写法
    owner = db.relationship(
        'User',
        primaryjoin='and_(foreign(Pack.uid) == remote(User.id),'
            'Pack.deleted_at == 0)',
        single_parent=True,
        backref=db.backref('pack', uselist=False),
    )

    @property
    def area_list(self):
        return self.owner.attention_areas

    @property
    def attention_area_ids(self):
        return [area.id for area in self.owner.attention_areas]


class PackAct(BaseModel):
    """
    抢单套餐活动
    """
    __tablename__ = 'pack_act'

    pack_type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    pack_total = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    pack_cnt = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    start_at = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    end_at = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    enable = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)


class Partic(BaseModel):
    """
    参与项目的工程商记录
    """
    filter_dict = {
        'demand_id': (),
        'bid_win_at_begin': ('bid_win_at', '>='),
        'bid_win_at_end': ('bid_win_at', '<='),
    }

    __tablename__ = 'partic'

    demand_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    supplier_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)

    # NOTE 如果是采购项目，招标中的status为10，中标的为200，未中标的依旧为10
    status = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)

    # 反馈信息
    feedback = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 反馈内容(当反馈类型为其他时有值)
    feedback_content = db.Column(db.String(1024), default='', server_default='')
    # 反馈审核信息
    feedback_status = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 反馈时间
    feedback_at = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 驳回理由
    feedback_refuse_content = db.Column(db.String(1024), default='', server_default='')
    # 是否返点
    is_revert = db.Column(
        db.Boolean(), default=False, server_default=text("false")
    )
    # 采购订单报价
    offer = db.Column(db.DECIMAL(12, 2), default=0, server_default=text("'0'"))
    # 报价时间
    offer_at = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 采购单是否违约
    is_breach = db.Column(
        db.Boolean(), default=False, server_default=text("false")
    )
    # 联系状态 0-未联系 1-已联系
    contact_state = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 联系时长(s)
    contact_seconds = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 竞价成功时间（时间戳）
    bid_win_at = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 竞拍时的项目状态
    demand_status = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 用户出价时的成就值(用于爆灯后做竞价排名，避免后续成就值变化影响排名)
    credit = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 跟进状况
    follow_type = db.Column(db.Integer, default=FollowType.KEEP.value, server_default=text("'2'"))
    # 跟进信息
    follow_msg = db.Column(db.String(1024), default='', server_default='')
    # 发布次数
    published_count = db.Column(db.Integer, default=1, server_default=text("'0'"))
    # 派单的企业号
    enterprise_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    enterprise_level = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 超时未抢单/未联系(包括派单, 抢单)
    partic_timeout = db.Column(
        db.Boolean(),
        default=False,
        server_default=text("false")
    )
    # 是否机器人
    is_robot = db.Column(
        db.Boolean(),
        default=False,
        server_default=text("false")
    )
    # 是否养成
    is_training = db.Column(
        db.Boolean(),
        default=False,
        server_default=text("false")
    )
    # 使用的代金券id, 0 表示未使用代金券
    # 消耗代金券使用队列方式，所以能避免一券多用的情况(不做唯一索引)
    voucher_id = db.Column(db.Integer(), default=0, server_default=text("'0'"))

    # 锁单使用的代金券id
    locked_voucher_id = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )

    # 获取真实号码
    apply_real_number_state = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 获取时间
    apply_real_number_at = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 同一产品类下名字唯一
    __table_args__ = (db.UniqueConstraint('demand_id', 'supplier_id', 'published_count'),)

    # 抢单记录与工程商关联
    owner = db.relationship(
        'User',
        primaryjoin='and_(foreign(Partic.supplier_id) == remote(User.id),'
                    'Partic.deleted_at==0)',
        single_parent=True,
        backref=db.backref('partics', lazy='dynamic'),
    )

    # 抢单记录与项目关联
    demand = db.relationship(
        'Demand',
        primaryjoin='and_(foreign(Partic.demand_id) == remote(Demand.id),'
                    'Partic.deleted_at==0)',
        single_parent=True,
        backref=db.backref('partics', lazy='dynamic'),
    )

    @property
    def bid_win_at_display(self):
        if self.bid_win_at:
            return datetime.fromtimestamp(self.bid_win_at)
        return ''

    @property
    def feedback_at_display(self):
        if self.feedback_at:
            return datetime.fromtimestamp(self.feedback_at)
        return ''

    @property
    def status_display(self):
        return ParticStatus(self.status).display

    @property
    def bid_status_display(self):
        return BidStatusDisplay.get(self.status, '未知')

    @property
    def feedback_display(self):
        return Feedback(self.feedback).display

    @property
    def feedback_status_display(self):
        if self.feedback:
            return FeedbackStatus(self.feedback_status).display
        return ''

    @property
    def offer_at_datetime(self):
        return (
            datetime.fromtimestamp(self.offer_at)
            if self.offer_at else 0
        )

    @property
    def is_breach_display(self):
        return '是' if self.is_breach else '否'

    @property
    def follow_type_display(self):
        return FollowType(self.follow_type).display

    @property
    def feedback_rate(self):
        if not self.feedback_at:
            return 0
        delay = self.feedback_at - self.bid_win_at
        # 30分钟以内100%
        if delay <= 30 * 60:
            return 1
        # 30分钟到6小时内50%
        elif delay <= 6 * 60 * 60:
            return 0.5
        # 6小时到24小时为20%
        elif delay <= 24 * 60 * 60:
            return 0.2
        # 超过24小时为0
        else:
            return 0

    @property
    def punish_bid_point(self):
        return math.floor(float(self.offer) * self.feedback_rate)

    @property
    def hide_nickname(self):
        """
        竞标中显示的名字
        规则为该抢单的创建时间后五位+抢单人id最后一位
        :return:
        """
        return '抢单人 {}{}'.format(
            str(self.created_at)[-3:],
            str(self.supplier_id)[-1]
        )

    @property
    def demand_status_display(self):
        return DemandStatus(self.demand_status).display

    @property
    def enterprise_name(self):
        from app.enterprise.models import Enterprise
        enterprise = Enterprise.get_by_id(self.enterprise_id)
        if enterprise:
            return enterprise.remark or enterprise.enterprise_name
        return ''

    @property
    def apply_real_number_state_display(self):
        return ApplyRealNumberState(self.apply_real_number_state).display

    @staticmethod
    def clear_demand_partic_cache_data(redis_obj, demand_id):
        """
        created by chenhj at 09/01/2018

        """
        keys = redis_obj.keys(PARTIC_CACHE_KEY_PREFIX.format(demand_id=demand_id))
        if keys:
            redis_obj.delete(*keys)

    @classmethod
    def demand_offer(cls, demand, supplier_id, offer):
        partic = cls.get_by_args(
            demand_id=demand.id,
            supplier_id=supplier_id
        )
        if partic:
            partic.offer = offer
            if partic.deleted_at:
                demand.bid_cnt += 1
            partic.deleted_at = 0
            partic.offer_at = int(time.time())
        else:
            cls.create(
                demand_id=demand.id,
                supplier_id=supplier_id,
                offer=offer,
                status=ParticStatus.WAITING.value,
                offer_at=int(time.time())
            )
            demand.bid_cnt += 1

    @classmethod
    def demand_partics(
            cls,
            demand,
            published_count=None,
            dispatch=False,
            ignore_training=True):
        """
        create by ld_wu 2017-10-17
        获取参与某项目的抢单记录
        过滤了拉新写入的记录（即潜在工程商的记录）
        """
        # NOTE by chenhj at 04/01/2018
        # 所有派单的人都不在这个方法中, 为了让前端方便
        if not published_count:
            published_count = demand.published_count
        partics = list()
        offer_desc = cls.query.filter(
            cls.demand_id == demand.id,
            cls.deleted_at == 0,
            cls.status != ParticStatus.TALKED.value,
            cls.published_count == published_count
        ).join(
            User, User.id == cls.supplier_id
        ).filter(
            User.type == UserType.ENGINEER.value
        )
        if ignore_training:
            offer_desc = offer_desc.filter(
                cls.is_training.__eq__(False)
            )

        if not dispatch:
            offer_desc = offer_desc.filter(
                cls.demand_status.notin_([
                    DemandStatus.DISPATCHING.value, DemandStatus.MASTER_DISPATCHING.value
                ])
            )

        offer_desc = offer_desc.filter(
            cls.demand_status == DemandStatus.ONGOING.value
        ).order_by(
            cls.offer.desc(),
            cls.credit.desc(),
            cls.id.asc()
        )

        offer_at_asc = cls.query.filter(
            cls.demand_id == demand.id,
            cls.deleted_at == 0,
            cls.demand_status != DemandStatus.ONGOING.value,
            cls.status != ParticStatus.TALKED.value,
            cls.published_count == published_count
        ).join(
            User, User.id == cls.supplier_id
        ).filter(
            User.type == UserType.ENGINEER.value
        ).order_by(
            cls.offer_at.asc()
        )
        if ignore_training:
            offer_at_asc = offer_at_asc.filter(
                cls.is_training.__eq__(False)
            )

        if not dispatch:
            offer_at_asc = offer_at_asc.filter(
                cls.demand_status.notin_([
                    DemandStatus.DISPATCHING.value, DemandStatus.MASTER_DISPATCHING.value
                ])
            )

        partics.extend(
            offer_desc.order_by(
                cls.offer_at.desc()
            ).all())
        if offer_at_asc:
            partics.extend(offer_at_asc.all())
        return partics

    @classmethod
    def partic_status(cls, demand, user):
        """
        create by ld_wu 2017-12-14
        返回当前用户的抢单状态
        """
        if not user:
            return ParticStatus.DRAFT.value
        partic = cls.query.filter(
            cls.demand_id == demand.id,
            cls.supplier_id == user.id,
            cls.deleted_at == 0,
            cls.status != ParticStatus.TALKED.value
        ).first()
        return partic.status if partic else ParticStatus.DRAFT.value

    @classmethod
    def month_reason_feedback_refuse_cnt(cls, user_id):
        """
        created by chenhj at 2018/4/2
        计算用户本月已被驳回的有理由反馈次数
        """
        today = date.today()
        month_first_day = datetime(today.year, today.month, 1, 0, 0, 0)
        start_timestamp = int(month_first_day.strftime('%s'))
        return cls.query.filter(
            cls.supplier_id == user_id,
            cls.feedback_at >= start_timestamp,
            cls.feedback_status == FeedbackStatus.REFUSE.value
        ).count()

    @classmethod
    def demand_winner_count(cls, demand_id):
        """
        create by ld_wu 2018-07-09
        项目中标人数（分配多少家）
        :param demand_id:
        :return:
        """
        return cls.query.filter(
            cls.demand_id == demand_id,
            cls.status.in_(
                [
                    ParticStatus.SUCCEED.value,
                    ParticStatus.NOT_CONTACT.value,
                    ParticStatus.BALANCE_NOT_ENOUGH.value
                ]
            ),
            cls.follow_type != FollowType.GIVE_UP.value,
            cls.is_robot.__eq__(False),
            cls.is_training.__eq__(False)
        ).count()

    def feedback_pass(self):
        """
        created by chenhj at 2018/4/2

        """
        self.feedback_status = FeedbackStatus.PASS.value
        pack = Pack.get_by_args(uid=self.supplier_id, deleted_at=0)
        demand = Demand.get_by_id(self.demand_id)
        self.is_revert = True
        # 跟进状态改为放弃跟进
        self.follow_type = FollowType.GIVE_UP.value

        # 项目进入回收站
        demand.recovery_demand(self.id, self.published_count)

        if pack:
            supplier = User.get_by_id(self.supplier_id)
            if self.demand_status not in (
                DemandStatus.DISPATCHING.value, DemandStatus.MASTER_DISPATCHING.value
            ):
                # 中标的可以退还所有，超时未联系的只返惩罚点
                # 惩罚点计算
                offer = int(self.offer)
                punish_bid = math.floor(offer * 0.3) or 1
                add_point = math.floor(
                    offer
                    if self.status == ParticStatus.SUCCEED.value
                    else punish_bid
                )
                pack.bid_point += add_point
                BidCostRecord.create(
                    user_id=self.supplier_id,
                    cost_type=BidCostType.FEED_BACK.value,
                    bid_point=add_point,
                    left_bid_point=pack.bid_point,
                    demand_id=self.demand_id
                )

                if self.published_count > 1:
                    # 二次发布的项目如果退款以后需要减少抢单成功数量
                    renewable_demand = RenewableDemand.get_by_args(
                        demand_id=self.demand_id
                    )
                    to_decrease = (
                        renewable_demand and
                        renewable_demand.recovery_success_count > 0 and
                        renewable_demand.operated_status == OperatedStatus.REPUBLISH.value
                    )
                    if to_decrease:
                        renewable_demand.recovery_success_count -= 1

                current_app.zone_stat_exchanger.exchange_send(json.dumps({
                    'return_bid_point': add_point,
                    'return_bid_user_id': supplier.id,
                    'war_zone_id': supplier.war_zone_id
                }))
                current_app.common_producer.send(
                    json.dumps(
                        dict(
                            backend='bid_stock_stat',
                            func='day_stat',
                            kwargs=dict(
                                stat_type='ret_bid_point',
                                stat_cnt=add_point,
                            )
                        )
                    )
                )

                if self.feedback != Feedback.NO_REASON.value:
                    # 返点后发送通知(无理由因为直接通过, 所以不通知)
                    MessageNotify.approval_notify(
                        demand, supplier, self, add_point=add_point, session=db.session
                    )
            # 解绑虚拟号
            from app.axb.models import VirtualNumber
            VirtualNumber.unbind_virtual_by_partic(self)

    def feedback_refuse(self, minus_credit, feedback_refuse_content):
        """
        created by chenhj at 2018/4/2

        """
        self.feedback_status = FeedbackStatus.REFUSE.value
        supplier = User.get_by_id(self.supplier_id)
        demand = Demand.get_by_id(self.demand_id)
        supplier.credit -= minus_credit
        self.feedback_refuse_content = feedback_refuse_content
        MessageNotify.approval_notify(
            demand,
            supplier,
            self,
            minus_credit=minus_credit,
            session=db.session
        )

        # 判断如果本月的驳回次数已经达到了3次, 则查询此用户是否还有未审核的反馈
        # 直接驳回
        if Partic.month_reason_feedback_refuse_cnt(self.supplier_id) >= MONTHLY_REFUSE_MAX:
            pending_feedback_data = Partic.query.filter(
                Partic.supplier_id == self.supplier_id,
                Partic.status == ParticStatus.SUCCEED.value,
                Partic.feedback_status == FeedbackStatus.PENDING.value
            ).all()
            for each in pending_feedback_data:
                each.feedback_refuse_content = '本月已有3次退款驳回记录，暂时无法退款'
                each.feedback_status = FeedbackStatus.REFUSE.value
                demand = Demand.get_by_id(each.demand_id)
                MessageNotify.approval_notify(
                    demand, supplier, each, session=db.session
                )


TmpUser = namedtuple('TmpUser', ['phone_area_name', 'com', 'id', 'avatar_url'])


class Follow(ExtensionalModel):

    __tablename__ = 'follows'

    # 关注人
    follower_id = db.Column(
        db.Integer(),
        db.ForeignKey('user.id'), primary_key=True
    )
    # 被关注人
    followed_id = db.Column(
        db.Integer(),
        db.ForeignKey('user.id'), primary_key=True
    )
    alias = db.Column(
        db.String(32), default='', server_default=text("''")
    )
    has_set_alias = db.Column(db.Boolean(), default=False)

    @property
    def friend(self):
        user = User.get_by_id(self.follower_id)
        return user if user else TmpUser(
            phone_area_name='',
            com='',
            id=0,
            avatar_url=''
        )


class User(BaseModel):
    """
    用户
    """
    __tablename__ = 'user'

    search_keys = ('nickname', 'com', )

    filter_dict = {
        'id': (),
        'wx_openid': (),
        'type': (),
        'mobile': (),
        'gender': (),
        'prov': (),
        'city': (),
        'bind_wx_openid': (),
        'active': (),
        'is_virt': (),
        'status': (),
        'sms_marketing_level': (),
        'black': (),
        'level': (),
        'war_zone_id': (),
        'respond_datetime_begin': ('sms_marketing_respond_datetime', '>='),
        'respond_datetime_end': ('sms_marketing_respond_datetime', '<='),
    }

    id = db.Column(db.Integer, primary_key=True)
    area_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    im_token = db.Column(
        db.String(32, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    push_reg_id = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    wx_openid = db.Column(
        db.String(128, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=1)
    level = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    mobile = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        unique=True,
        server_default=text("''"),
        default=''
    )
    nickname = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    # 新增用户时默认为男
    gender = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=1)
    prov = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default='',
        index=True
    )
    city = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    addr = db.Column(
        db.String(128, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    industry = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    com = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    pos = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    avatar = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_name = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_acct_name = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_acct_num = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bal = db.Column(
        db.Numeric(16, 2), nullable=False,
        server_default=text("'0.00'"),
        default=Decimal('0.00')
    )
    # 0 - 未绑定过微信; 1 - 绑定过微信
    bind_wx_openid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    no_sms = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    callback_at = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    active = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    is_virt = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    status = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    remark = db.Column(
        db.String(256, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    # 最近登录时间
    recent_login_at = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 积分
    credit = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 违约次数
    breach_count = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 是否设置过业务范围
    has_set_business = db.Column(db.Boolean, default=False, server_default=text("FALSE"))

    # 分配加微信好友的人(已分配id不为空的话说明已加好友)
    bind_user_id = db.Column(db.Integer())
    bind_user_name = db.Column(db.String(64), default='')
    bind_date = db.Column(db.Date())

    # 短信营销相关字段 add by zhouhl 2017-10-24
    # 是否短信营销对象
    sms_marketing_obj = db.Column(
        db.Boolean, nullable=False, server_default=text('FALSE'), default=False
    )
    # 首次短信营销日期
    sms_marketing_first_date = db.Column(db.Date(), nullable=True)
    # 短信营销是否有回应
    sms_marketing_respond = db.Column(
        db.Boolean, nullable=False, server_default=text('FALSE'), default=False
    )
    # 营销工程商分级
    sms_marketing_level = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 最近发短信日期
    sms_current_send_date = db.Column(db.Date(), nullable=True)
    # 最近回应时间
    sms_marketing_respond_datetime = db.Column(db.DateTime(), nullable=True)
    # 是否付费充值过
    # FIXME by chenhj at 2017/12/12 五折优惠功能上线前付过款的用户此字段才为true
    has_paid = db.Column(
        db.Boolean(), default=False, server_default=text('FALSE')
    )
    # 是否被拉黑
    black = db.Column(db.Boolean, default=False,  server_default=text('FALSE'))
    # 是否内部用户
    is_internal = db.Column(db.Boolean, default=False, server_default=text('FALSE'))
    war_zone_id = db.Column(db.ForeignKey('war_zone.id'))
    # 手机归属地
    phone_area_id = db.Column(db.ForeignKey('area.id'))

    # 是否正在养成中
    training_status = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 养成指数(ceil(总付费金额/90) - floor(总抢单数/30) * 30 + 30),
    #  总抢单数包括退单数和超时未联系数等, 不包括排队
    training_index = db.Column(db.Integer, default=0, server_default=text("'0'"))

    # 浏览过的项目
    scan_demands = db.relationship(
        "Demand",
        secondary=supplier_demand_table
    )

    phone_area = db.relationship(
        'Area',
        backref=db.backref('zone_users', lazy='dynamic')
    )

    # 该用户关注的人
    followed = db.relationship(
        'User',
        secondary=Follow.__table__,
        primaryjoin=id == Follow.follower_id,
        secondaryjoin=id == Follow.followed_id,
        backref=db.backref('followers', lazy='dynamic'),
        # sqlalchemy.exc.CircularDependencyError报错的官方解决方案
        post_update=True,
    )

    attention_areas = db.relationship(
        'Area',
        secondary=user_to_area,
        backref=db.backref('area_users', lazy='dynamic')
    )

    @property
    def is_bronze_medal(self):
        """
        add by zhouhl 2018-04-11
        判断该用户当前是否铜牌
        """
        return self.highest_level >= UserLevel.LEVEL_3.value

    @property
    def enterprise_info(self):
        enterprise = self.enterprises.first()
        return enterprise.id if enterprise else 0

    @property
    def bronze_medal_valid(self):
        """
        add by zhouhl 2018-04-11
        判断该用户当前的铜牌是否有效
        """
        if self.highest_level >= UserLevel.LEVEL_3.value:
            return True

        pack = self.pack
        if not pack:
            return False
        return self.level == UserLevel.LEVEL_3.value

    @property
    def attention_areas_display(self):
        areas = self.attention_areas
        if len(areas) > MAX_AREA_DISPLAY:
            areas = areas[:MAX_AREA_DISPLAY]
            display = ','.join([area.name for area in areas])
            display = '{}...'.format(display)
        else:
            display = ','.join([area.name for area in areas])
        return display

    @property
    def attention_areas_all_display(self):
        areas = self.attention_areas
        display = ','.join([area.name for area in areas])
        return display

    @property
    def rank(self):
        """
        返回该工程商同等级全国绑定微信用户的积分排名
        :return:
        """
        return User.query.filter(
            User.type == UserType.ENGINEER.value,
            User.active == UserActive.ACTIVE.value,
            User.level == self.level,
            User.credit > self.credit,
            User.wx_openid != ''
        ).count() + 1

    @property
    def pass_cnt(self):
        """
        返回成就值小于该工程商的用户数
        """
        return User.query.filter(
            User.type == UserType.ENGINEER.value,
            User.active == UserActive.ACTIVE.value,
            User.credit < self.credit,
            User.credit > 0,
        ).count()

    @property
    def gender_display(self):
        return Gender(self.gender).display

    @property
    def type_display(self):
        return UserType(self.type).display

    @property
    def active_display(self):
        return Choice(self.active).display

    @property
    def level_display(self):
        """
        create by ld_wu 2017-11-21
        用户等级显示
        :return:
        alter by zhouhl 2018-04-26 临时处理方案：地方合作伙伴返回铁牌
        """
        return UserLevel(self.get_user_level).display

    @property
    def get_user_level(self):
        """
        add by zhouhl 2018-04-26
        临时处理方案：地方合作伙伴返回铁牌
        """
        return UserLevel.LEVEL_2.value \
            if self.level == UserLevel.LEVEL_6.value else self.level

    @property
    def is_virt_display(self):
        return Choice(self.is_virt).display

    @property
    def status_display(self):
        return UserStatus(self.status).display

    @property
    def training_status_display(self):
        return UserTrainingStatus(self.training_status).display

    @property
    def is_engineer(self):
        """
        create by ld_wu 2017-08-31
        是否为工程商或潜在工程商
        :return:
        """
        return self.type in (UserType.ENGINEER.value, UserType.POTENTIAL.value)

    @property
    def avatar_url(self):
        if self.avatar:
            host = os.environ.get('img_host')
            return urllib.parse.urljoin(host, self.avatar)
        return self.avatar

    @property
    def sms_marketing_obj_display(self):
        return '是' if self.sms_marketing_obj else '否'

    @property
    def sms_marketing_respond_display(self):
        return '是' if self.sms_marketing_respond else '否'

    @property
    def sms_marketing_level_display(self):
        return SmsMarketingLevel(self.sms_marketing_level).display

    @property
    def info_bid_count(self):
        return UserInfo.default_user_info(self.id).bid_count

    @property
    def total_pay_amount(self):
        """
        总付费金额
        :return:
        """
        res = Bill.query.filter(
            Bill.uid == self.id,
            Bill.type == BillType.INCOMING.value,
            Bill.sub_type.in_([BillSubType.BUY_PACK.value, BillSubType.HALF_PACK.value]),
            Bill.amt > 0,
            Bill.status == BillStatus.PAID.value
        ).with_entities(func.sum(Bill.amt)).first()[0]
        return res or 0

    @property
    def total_opportunity_count(self):
        """
        总机会数(除了排队的. 只要获得过用户联系方式, 都算入)
        :return:
        """
        return Partic.query.filter(
            Partic.supplier_id == self.id,
            Partic.status.notin_(
                [ParticStatus.DRAFT.value, ParticStatus.WAITING.value, ParticStatus.TALKED.value]
            )
        ).count()

    @property
    def close_deal_count(self):
        """
        总成单数(申报并通过的)
        :return:
        """
        return DemandFinishImg.query.filter(
            DemandFinishImg.user_id == self.id,
            DemandFinishImg.state == DemandFinishImgState.CHECKED.value
        ).count()

    @property
    def user_demand_unit_price(self):
        """
        货单价
        :return:
        """
        if self.total_opportunity_count == 0:
            return 0
        return '%.2f' % (float(self.total_pay_amount) / self.total_opportunity_count)

    @property
    def total_gift_partic_count(self):
        """
        总赠送单数
        :return:
        """
        return Partic.query.filter(
            Partic.supplier_id == self.id,
            Partic.status.notin_(
                [ParticStatus.DRAFT.value, ParticStatus.WAITING.value, ParticStatus.TALKED.value]
            ),
            Partic.is_training.is_(True)
        ).count()

    @property
    def info_consult_count(self):
        return UserInfo.default_user_info(self.id).consult_count

    @property
    def info_com_summary(self):
        return UserInfo.default_user_info(self.id).com_summary

    @property
    def has_install(self):
        return UserTag.default_tag(self.id).install

    @property
    def has_repair(self):
        return UserTag.default_tag(self.id).repair

    @property
    def has_rent(self):
        return UserTag.default_tag(self.id).rent

    @property
    def tag_phone_contact(self):
        return UserTag.default_tag(self.id).phone_contact

    @property
    def tag_add_wechat(self):
        return UserTag.default_tag(self.id).add_wechat

    @property
    def reason_left_times(self):
        return (
            MONTHLY_REFUSE_MAX -
            Partic.month_reason_feedback_refuse_cnt(self.id)
        )

    @property
    def can_popup_prompt(self):
        """
        create by ld_wu 2018-05-04
        是否能弹出赠送套餐提示
        1、从未绑定微信的用户
        2、拉新用户
        :return:
        """

        return (
            redis_client.sismember(GIFT_PACK_USER_SET, self.id)
            and not redis_client.sismember(HAD_POPUP_PROMPT_SET, self.id)
        )

    @property
    def highest_level(self):
        """
        若有有效企业,则返回企业等级,否则返回个人等级
        :return:
        """
        if self.enterprises.first() and self.enterprises.first().valid:
            return self.enterprises.first().level
        return self.level

    @classmethod
    def get_sup_count_by_cond(cls, prov, level=None):
        """
        created by chenhj at 2017/8/14
        根据条件获取工程商数量
        """
        if not level:
            return cls.query.filter(
                cls.type == UserType.ENGINEER.value,
                cls.active == UserActive.ACTIVE.value,
                cls.prov.like('{}%'.format(prov))
            ).count()
        return cls.query.filter(
            cls.type == UserType.ENGINEER.value,
            cls.active == UserActive.ACTIVE.value,
            cls.prov == prov,
            cls.level == level
        ).count()

    @classmethod
    def get_certify_sup_count_by_city(cls, city_id):
        """
        created by chenhj at 2017/11/13

        """
        tmp_area = Area.get_by_id(str(city_id))
        return tmp_area.area_users.filter(
            cls.type == UserType.ENGINEER.value,
            cls.active == UserActive.ACTIVE.value,
            cls.status == UserStatus.PASS.value,
            cls.deleted_at == 0,
            cls.black.is_(False)
        ).count()

    @classmethod
    def get_all_sup_count(cls, level=None):
        """
        created by chenhj at 2017/8/14
        若level不为None, 则表示取全国同等级的工程商数量
        """
        if not level:
            return cls.query.filter(
                cls.type == UserType.ENGINEER.value,
                cls.active == UserActive.ACTIVE.value,
            ).count()
        return cls.query.filter(
            cls.type == UserType.ENGINEER.value,
            cls.active == UserActive.ACTIVE.value,
            cls.level == level
        ).count()

    def get_all_valid_demands(self):
        """
        created by chenhj at 2017/8/14
        取除草稿状态外的自己的需求
        """
        return self.demands.filter(
            Demand.status != DemandStatus.DRAFT.value,
            Demand.deleted_at == 0
        )

    def get_done_and_ongoing_demands(self):
        """
        created by chenhj at 2017/8/14

        """
        return self.demands.filter(
            Demand.status.notin_([
                DemandStatus.DRAFT.value,
                DemandStatus.UNCHECKED.value,
                DemandStatus.INVALID.value
            ]),
            Demand.deleted_at == 0
        )

    def get_partic_demands(self):
        """
        created by chenhj at 2017/8/14

        """
        partics = self.partics
        demand_ids = [x.demand_id for x in partics]
        if demand_ids:
            return Demand.query.filter(
                Demand.id.in_(demand_ids),
                Demand.status.notin_([
                    DemandStatus.DRAFT.value,
                    DemandStatus.UNCHECKED.value,
                    DemandStatus.INVALID.value
                ]),
                Demand.deleted_at == 0
            ).order_by(Demand.published_at.desc())
        return []

    @classmethod
    def get_or_create(cls, mobile):
        user = cls.get_by_args(
            mobile=mobile
        ) or cls.create(
            mobile=mobile,
            type=UserType.CLIENT.value,
        )
        return user

    @classmethod
    def get_user_by_mobiles(cls, mobiles):
        """
        create_by ld_wu 2018-01-08
        通过电话列表获取用户列表
        """
        if mobiles:
            return cls.query.filter(
                cls.mobile.in_(mobiles),
                cls.deleted_at == 0
            )
        return list()

    @classmethod
    def invite_user_query(cls, is_bind=None, send_date_begin=None, send_date_end=None, status=None):
        """
        拉新工程商
        :return:
        """
        user_query = cls.query.filter(
            cls.sms_marketing_obj.is_(True),
            cls.sms_marketing_first_date.isnot(None)
        ).outerjoin(
            UserMarketingInfo,
            cls.id == UserMarketingInfo.user_id
        ).order_by(
            UserMarketingInfo.current_send_date.desc()
        ).with_entities(
            cls.id.label('user_id'),
            cls.mobile,
            cls.wx_openid,
            cls.nickname,
            cls.city,
            cls.sms_marketing_level,
            UserMarketingInfo.view_count,
            UserMarketingInfo.total_send_count,
            UserMarketingInfo.bid_click_count,
            UserMarketingInfo.attend_click_count,
            cls.sms_marketing_first_date,
            cls.sms_current_send_date,
            cls.sms_marketing_obj,
        )
        if is_bind:
            user_query = user_query.filter(
                cls.wx_openid != ''
            )
        if send_date_begin:
            user_query = user_query.filter(
                cls.sms_marketing_first_date >= send_date_begin
            )

        if send_date_end:
            user_query = user_query.filter(
                cls.sms_marketing_first_date <= send_date_end
            )
        return user_query

    @classmethod
    def invite_user_query_july(cls, is_bind=None, send_date_begin=None, send_date_end=None, status=None):
        """
        拉新工程商
        :return:
        """
        user_query = cls.query.join(
            UserMarketingInfoJuly,
            cls.id == UserMarketingInfoJuly.user_id
        ).order_by(
            UserMarketingInfoJuly.current_send_date.desc()
        ).with_entities(
            cls.id.label('user_id'),
            cls.mobile,
            cls.wx_openid,
            cls.nickname,
            cls.city,
            cls.sms_marketing_level,
            UserMarketingInfoJuly.view_count,
            UserMarketingInfoJuly.total_send_count,
            UserMarketingInfoJuly.bid_click_count,
            UserMarketingInfoJuly.attend_click_count,
            UserMarketingInfoJuly.status,
            UserMarketingInfoJuly.first_send_date,
            UserMarketingInfoJuly.current_send_date,
        )
        if is_bind:
            user_query = user_query.filter(
                cls.wx_openid != ''
            )

        if send_date_begin:
            user_query = user_query.filter(
                UserMarketingInfoJuly.first_send_date >= send_date_begin
            )

        if send_date_end:
            user_query = user_query.filter(
                UserMarketingInfoJuly.first_send_date <= send_date_begin
            )

        if status != NotChoices[0][0]:
            user_query = user_query.filter(
                UserMarketingInfoJuly.status == status
            )
        return user_query

    @classmethod
    def user_mapping(cls, user_ids):
        """
        create by ld_wu 2018-01-08
        获取用户的公司头像等信息
        用于好友列表或者通讯录等信息拼接
        """
        users = cls.query.filter(
            cls.id.in_(user_ids)
        ).all()

        user_mapping = {each.id: dict(
            user_id=each.id,
            com=each.com,
            avatar=each.avatar_url,
            nickname=each.nickname,
            mobile=each.mobile,
            phone_area_name=each.phone_area.name if each.phone_area else ''
        ) for each in users}
        return user_mapping

    def is_following(self, user):
        return True if db.session.execute(
            Statement.find_follow_relation_sql,
            {
                'follower_id': self.id,
                'followed_id': user.id,
            }
        ).fetchone() else False

    def is_followed_by(self, user):
        return True if db.session.execute(
            Statement.find_follow_relation_sql,
            {
                'follower_id': user.id,
                'followed_id': self.id,
            }
        ).fetchone() else False

    def be_engineer_sms_handler(self):
        """
        create by ld_wu 2018-04-27
        成为工程商后，拉新促活的处理
        :return:
        """
        # 非拉新用户直接返回
        if not self.sms_marketing_obj:
            return
        # 未分级，进行分级
        if self.sms_marketing_level == SmsMarketingLevel.UNCLASSIFIED.value:
            self.sms_marketing_level = SmsMarketingLevel.IS_ENGINEER.value
        else:
            # 停止订单拉新短信
            info = UserMarketingInfo.get_by_args(user_id=self.id)
            if info:
                info.status = MarketingStatus.SUSPEND.value

    def gift_bid_point(self):
        """
        赠送试用套餐
        首次绑定或者拉新用户首次登录
        :return:
        """

        if not self.pack:
            # 首次赠送
            Pack.create(
                uid=self.id,
                bid_point=TRIAL_BID_POINT,
                accum_bid_point=TRIAL_BID_POINT,
            )
            area_ids1 = Area.get_city_ids(self.prov)
            area_ids2 = []

            self.attention_areas = Area.get_list_by_ids(
                list(set(area_ids1) | set(area_ids2))
            ).all()

            # 赠送代金券
            Voucher.gift_voucher(self.id)

            redis_client.sadd(GIFT_PACK_USER_SET, self.id)

    def total_invite_messages(self):
        """
        create by ld_wu 2018-05-28
        拉新短信发送总数
        :return:
        """
        count = 0
        if self.sms_marketing_obj:
            if self.sms_marketing_first_date:
                if self.sms_current_send_date:
                    delay_days = (
                        self.sms_current_send_date - self.sms_marketing_first_date
                    ).days
                    # Note 根据common.schedule.opc_task中的MsgMap
                    # 拉新短信的发送规则来计算已发短信的数量
                    if delay_days >= 11:
                        count = 5
                    elif delay_days >= 6:
                        count = 4
                    elif delay_days >= 3:
                        count = 3
                    elif delay_days >= 1:
                        count = 2
                    else:
                        count = 1
                # 避免手动改数据导致没有设置最近发送时间
                else:
                    count = 1
        return count

    def transfer_bid_point(self, to_user, bid_point, remark):
        """
        create by ld_wu 2018-05-29
        im 转账
        成功返回True, 失败返回False（汇币不足）
        """
        transfer_pack = self.pack
        if transfer_pack.bid_point < bid_point:
            return False
        transfer_pack.bid_point -= bid_point
        # 写入转账方的消耗记录
        BidCostRecord.create(
            cost_type=BidCostType.TRANSFER.value,
            user_id=self.id,
            bid_point=bid_point,
            left_bid_point=transfer_pack.bid_point,
            interact_user_id=to_user.id,
            remark=remark
        )

        # 接收方逻辑
        receive_pack = to_user.pack
        receive_pack.bid_point += bid_point
        # 写入接收方的消耗记录
        BidCostRecord.create(
            cost_type=BidCostType.RECEIVE.value,
            user_id=to_user.id,
            bid_point=bid_point,
            left_bid_point=receive_pack.bid_point,
            interact_user_id=self.id,
            remark=remark
        )
        return True


class UserInfo(ExtensionalModel):
    """
    用户的扩展表
    只有认证过的工程商才需写入此表
    """
    __tablename__ = 'user_info'

    # 扩展表主键，用户表的外键
    user_id = db.Column(
        db.Integer(),
        db.ForeignKey('user.id'),
        primary_key=True
    )
    # 公司简介
    com_summary = db.Column(db.Text(), default='')
    # 成交次数（中标次数）
    bid_count = db.Column(
        db.Integer(), default=0, index=True, server_default=text("'0'")
    )
    # 咨询次数
    consult_count = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    level_3_notify = db.Column(db.Boolean, default=False, server_default=text("FALSE"))
    # 新用户不弹此弹框
    sms_alter_notify = db.Column(db.Boolean, default=True, server_default=text("FALSE"))
    # 无理由退款次数
    free_reason_feedback_cnt = db.Column(db.Integer(), default=3, server_default=text("'3'"))
    # app邀请人
    app_invite_user_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    app_root_invite_user_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 所属运营中心
    operation_center_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # app首次登录时间
    app_first_login_time = db.Column(db.DateTime)
    # app最后访问时间
    app_last_visit_time = db.Column(db.DateTime)
    # app登录版本号
    app_login_version = db.Column(db.String(32), default='', server_default=text("''"))

    @classmethod
    def default_user_info(cls, user_id):
        return cls.get_by_args(user_id=user_id) or cls.create(user_id=user_id)

    @classmethod
    def get_all_app_invite_users(cls):
        """
        created by chenhj at 2018/5/3

        """
        user_ids = cls.query.filter(
            cls.app_invite_user_id != 0,
        ).with_entities(
            cls.app_invite_user_id.distinct()
        )
        return User.get_list_by_ids(user_ids)

    @classmethod
    def get_all_app_root_invite_users(cls):
        """
        created by chenhj at 2018/5/3

        """
        user_ids = cls.query.filter(
            cls.app_root_invite_user_id != 0,
        ).with_entities(
            cls.app_root_invite_user_id.distinct()
        )
        return User.get_list_by_ids(user_ids)


class UserMarketingBase(ExtensionalModel):
    """
    create by ld_wu 2018-04-24
    用户拉新信息记录基类
    """
    __abstract__ = True

    id = db.Column(db.Integer(), primary_key=True)
    # 进站次数
    view_count = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 首次进站时间
    first_view_date = db.Column(db.Date())
    # 最近进站时间
    current_view_date = db.Column(db.Date())
    # 首次发送时间
    first_send_date = db.Column(db.Date(), index=True)
    # 最近发送时间
    current_send_date = db.Column(db.Date())
    # 总发送次数
    total_send_count = db.Column(db.Integer(), default=0)
    mobile = db.Column(db.String(16), default='')
    user_id = db.Column(db.Integer(), unique=True)
    # 关注省份
    area_id = db.Column(db.Integer(), index=True)
    # 状态(是否继发送等)
    status = db.Column(db.SMALLINT())
    # 点击出价次数
    bid_click_count = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 点击立即关注次数
    attend_click_count = db.Column(
        db.Integer(),  default=0, server_default=text("'0'")
    )
    # 进入待发队列的时间（用于判定进入当天不发短信）
    push_date = db.Column(db.Date(), default=date.today)

    @classmethod
    def get_or_create(cls, user_id):
        """
        create by ld_wu 2018-04-24
        :param user_id:
        :return:
        """

        info = cls.get_by_args(user_id=user_id)
        if not info:
            user = User.get_by_id(user_id)
            if user and user.sms_marketing_obj:
                area = Area.get_by_args(id=user.area_id)
                info = cls.create(
                    user_id=user_id,
                    mobile=user.mobile,
                    area_id=area.pid,
                    status=MarketingStatus.SUSPEND.value
                )
        return info

    @classmethod
    def update_view_count(cls, user_id):
        """
        create by ld_wu 2018-04-24
        更新进站次数
        :param user_id:
        :return:
        """
        info = cls.get_or_create(user_id)
        if info:
            today = date.today()
            if not info.first_view_date:
                info.first_view_date = today
            info.current_view_date = today
            info.view_count += 1


class UserMarketingInfo(UserMarketingBase):
    """
    create by ld_wu 2018-04-24
    用户拉新信息记录
    """

    __tablename__ = 'user_marketing_info'


class UserMarketingInfoJuly(UserMarketingBase):
    """
    add by zhouhl 2018-06-28
    七月份用户拉新的信息记录，复制原来的 UserMarketingInfo
    """
    __tablename__ = 'user_marketing_info_july'


class WxUser(BaseModel):
    """
    微信用户记录
    """
    __tablename__ = 'wxuser'

    filter_dict = {
        'subscribe': (),
        'invite_user_id': (),
        'subscribe_time_begin': ('subscribe_time', '>='),
        'subscribe_time_end': ('subscribe_time', '<='),
    }

    search_keys = ('nickname',)

    # 0-未关注 1-已关注
    subscribe = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0
    )
    openid = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default='',
        unique=True
    )
    nickname = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    sex = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0
    )
    language = db.Column(
        db.String(32, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    city = db.Column(
        db.String(32, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    province = db.Column(
        db.String(32, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    country = db.Column(
        db.String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    headimgurl = db.Column(
        db.String(256, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    subscribe_time = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    remark = db.Column(
        db.String(128, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    groupid = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    tagid_list = db.Column(db.JSON)
    # 额外字段, 供opc系统使用(在未绑定用户发送微信时使用)
    area_id = db.Column(db.Integer, default=0, server_default=text("'0'"))

    # 邀请相关信息
    invite_user_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    invite_state = db.Column(db.Integer, default=WxInviteState.NA.value, server_default=text("'-1'"))
    invite_time = db.Column(db.Integer, default=0)
    invite_buy_pack = db.Column(db.Boolean, default=False, server_default=text("FALSE"))
    invite_pack_price = db.Column(db.DECIMAL(16, 2), default=0, server_default=text("'0'"))
    # 是否地推用户
    is_ground = db.Column(
        db.Boolean(), default=False, server_default=text("FALSE")
    )
    # ip地址
    ip_address = db.Column(db.String(64), default='', server_default=text("''"))
    # 审核状态
    approval_status = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 距离(距离指定地点的距离)
    distance = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 经纬度
    coordinates = db.Column(db.String(64), default='', server_default=text("''"))
    # 定位地址
    address = db.Column(db.String(64), default='', server_default=text("''"))
    # 绑定时间
    certify_time = db.Column(db.Integer(), default=0)
    approval_user_id = db.Column(db.Integer())

    user = db.relationship(
        'User',
        primaryjoin='and_(foreign(WxUser.openid) == remote(User.wx_openid),'
                    'WxUser.subscribe == 1)',
        single_parent=True,
        backref=db.backref('wxuser', uselist=False),
    )

    invite_user = db.relationship(
        'User',
        primaryjoin='and_(foreign(WxUser.invite_user_id) == remote(User.id))',
        single_parent=True,
    )

    @classmethod
    def subscribe_count(cls):
        """
        返回总关注用户数
        :return:
        """
        return cls.query.filter_by(subscribe=1).count()

    @property
    def sex_display(self):
        if self.sex == 1:
            return '男'
        return '女'

    @property
    def subscribe_time_display(self):
        return datetime.fromtimestamp(self.subscribe_time)

    @property
    def subscribe_display(self):
        if self.subscribe == 0:
            return '否'
        return '已关注'

    @property
    def coordinates_display(self):
        if self.coordinates in ['0,0', '']:
            return '获取定位失败'
        else:
            return self.coordinates

    @property
    def is_in_range(self):
        """
        是否在展会范围内
        :return:
        """
        coordinates = self.coordinates.split(',')
        if coordinates and len(coordinates) == 2:
            [longitude, latitude] = coordinates
            longitude = float(longitude)
            latitude = float(latitude)
            return (121.0 < longitude < 122.0) and (30.0 < latitude < 32.0)
        return False

    @property
    def secret_mobile(self):
        user = self.user
        if user:
            mobile = user.mobile
            return mobile[:3] + '****' + mobile[-4:]
        else:
            if self.certify_time:
                cancel_user = CancelAttentionUser.get_by_args(
                    wx_openid=self.openid
                )
                if cancel_user:
                    return cancel_user.mobile[:3] + '****' + cancel_user.mobile[-4:]
        return '-'

    @property
    def invite_time_display(self):
        if self.invite_time:
            return datetime.fromtimestamp(self.invite_time)
        return ''

    @property
    def certify_time_display(self):
        if self.certify_time:
            return datetime.fromtimestamp(self.certify_time)
        return ''

    @property
    def approval_status_display(self):
        if self.approval_status == WxUserApprovalStatus.DEFAULT.value:
            if not self.subscribe:
                return '已取关'
        return WxUserApprovalStatus(self.approval_status).display

    @property
    def invite_state_display(self):
        return WxInviteState(self.invite_state).display

    @property
    def invite_buy_pack_display(self):
        if self.invite_buy_pack:
            return '已购买'
        return '未购买'

    @classmethod
    def get_unbind_wxuser_openids(cls, prov):
        """
        created by chenhj at 2017/8/29
        @:param prov: 省份: 如"广东省"
        """
        if not prov:
            return []
        area_id = Area.get_by_args(name=prov).id

        select_wxuser_sql = text("""
            select openid from wxuser where area_id=:area_id and openid not in (
              select wx_openid from user
            ) and subscribe = 1
        """)

        return db.session.execute(
            select_wxuser_sql,
            {
                'area_id': area_id
            }
        ).fetchall()

    @classmethod
    def get_all_invite_user_ids(cls, only_ground=False):
        """
        created by chenhj at 2017/9/20

        """
        query = cls.query.filter(
            cls.invite_user_id != 0
        ).with_entities(
            cls.invite_user_id.distinct()
        )
        if only_ground:
            query = query.filter(
                cls.is_ground.__eq__(True)
            )
        return [x[0] for x in query.all()]


class FrontendEventStat(BaseModel):
    """
    created by chenhj at 2017/8/23
    记录某个前端某个事件(由前端自行定义事件名)的执行次数
    """
    search_keys = ('event',)
    filter_dict = {
        'date': ()
    }

    __tablename__ = 'frontend_event_statistic'

    event = db.Column(db.String(512), default='')
    date = db.Column(db.Date, default=date.today)
    count = db.Column(db.Integer, default=1)


class PackSet(BaseModel):
    """
    created by chenhj at 2017/8/24
    套餐(弃用)
    """
    __tablename__ = 'pack_set'

    name = db.Column(db.String(128), unique=True)
    # 原价
    ori_price = db.Column(db.DECIMAL(16, 2), default=0)
    # 现价
    price = db.Column(db.DECIMAL(16, 2), default=0)
    # 电话积分
    call_point = db.Column(db.Integer, default=0)
    # 短信积分
    sms_point = db.Column(db.Integer, default=0)
    # 汇币
    bid_point = db.Column(db.Integer, default=0)
    # 套餐有效日
    days = db.Column(db.Integer, default=0)
    # 套餐标签(若有, 则前端可以显示)
    tag = db.Column(db.String(64), default='')

    @classmethod
    def get_least_set(cls):
        """
        created by chenhj at 2017/8/28
        获取最低价格套餐
        """
        return cls.query.order_by(cls.price)[0]


class PricePackSet(BaseModel):
    """
    created by yuanxing 2017/12/28
    金钱支付的套餐表
    """
    __tablename__ = 'price_pack_set'
    # 套餐名
    name = db.Column(db.String(128), unique=True)
    # 原价
    ori_price = db.Column(db.DECIMAL(16, 2), default=0)
    # 现价
    price = db.Column(db.DECIMAL(16, 2), default=0)
    # 汇币
    bid_point = db.Column(db.Integer, default=0)
    # 赠送汇币
    gift_bid_point = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 套餐标签
    tag = db.Column(db.String(64), default='')


class IncrementPackSet(BaseModel):
    """
    created by yuanxing 2017/12/28
    汇币支付的增值套餐表
    """
    __tablename__ = 'increment_pack_set'
    # 套餐名
    name = db.Column(db.String(128))
    # 增值套餐类型
    type = db.Column(db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 消耗汇币
    bid_point = db.Column(db.Integer, default=0)
    # 原消耗汇币
    ori_bid_point = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 新项目提醒
    sms_point = db.Column(db.Integer, default=0)
    # 派单点
    dispatch_point = db.Column(db.Integer, default=0)
    # 套餐有效期
    days = db.Column(db.Integer, default=0)
    # 是否为不限量包,默认是限量包
    not_limit = db.Column(db.Boolean, default=False, server_default=text('FALSE'))
    # 套餐标签(若有, 则前端可以显示)
    tag = db.Column(db.String(64), default='')

    @property
    def type_display(self):
        return IncrementPackType(self.type).display

    @property
    def not_limit_display(self):
        return '不限量' if self.not_limit else '限量'

    # 如果为不限量套餐，返回套餐有效期，否则返回永久有效
    @property
    def days_display(self):
        return self.days if self.not_limit else '永久有效'

    # 如果为不限量套餐，返回不限量，否则返回当前项目提醒数量
    @property
    def sms_point_display(self):
        if self.type in [IncrementPackType.SMS.value]:
            return '不限量' if self.not_limit else self.sms_point
        else:
            return '--'

    @property
    def dispatch_point_display(self):
        if self.type in [IncrementPackType.DISPATCH.value]:
            return self.dispatch_point
        else:
            return '--'


class UserTag(BaseModel):
    """
    create by ld_wu 2017-08-30
    工程商标签
    """

    __tablename__ = 'user_tags'

    # 工程商id，每个工程商只对应一条标签记录
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'), unique=True)
    # 关注公众号状态
    follow_state = db.Column(db.Integer(), default=0)
    # 总体意愿
    aspiration = db.Column(db.Integer(), default=0)
    # 对金牌会员的预期
    gold_aspiration = db.Column(db.Integer(), default=0)
    # 对成为供应商的意愿
    supplier_aspiration = db.Column(db.Integer(), default=0)
    # 付费的意愿
    pay_aspiration = db.Column(db.Integer(), default=0)
    # 付费后的感受
    paid_feel = db.Column(db.Integer(), default=0)
    # 专业度
    mastery = db.Column(db.Integer(), default=0)
    # 规模
    scale = db.Column(db.Integer(), default=0)
    # 联系频率
    contact_frequency = db.Column(db.Integer(), default=0)
    # 伤害程度
    injured = db.Column(db.Integer(), default=0)

    # 对万屏会的评价(多选， True则为选中标签)
    # 单少
    few_order = db.Column(db.Boolean(), default=False)
    # 单小
    small_order = db.Column(db.Boolean(), default=False)
    # 单不真实
    fake_order = db.Column(db.Boolean(), default=False)
    # 抢单人太多
    crowd_order = db.Column(db.Boolean(), default=False)
    # 规则不公平
    unfair_rule = db.Column(db.Boolean(), default=False)

    # 业务范围
    # 安装
    install = db.Column(db.Boolean(), default=True)
    # 维修
    repair = db.Column(db.Boolean(), default=True)
    # 租赁
    rent = db.Column(db.Boolean(), default=True)
    # 采购
    purchase = db.Column(
        db.Boolean(), default=True, server_default=text('TRUE')
    )

    # 已电话联系
    phone_contact = db.Column(db.Boolean, default=False, server_default=text('False'))
    # 已加微信
    add_wechat = db.Column(db.Boolean, default=False, server_default=text('False'))

    @classmethod
    def default_tag(cls, user_id):
        tag = cls.get_by_args(user_id=user_id) or cls.create(user_id=user_id)
        return tag

    @classmethod
    def user_mapping(cls, user_ids):
        tags = cls.query.filter(
            cls.user_id.in_(user_ids)
        )
        return {tag.user_id: tag for tag in tags}


class CancelAttentionUser(BaseModel):
    """
    add by zhouhl 2017-09-05
    取消关注用户
    """
    __tablename__ = 'cancel_attention_users'

    search_keys = ('nickname',)

    filter_dict = {
        'uid': (),
        'mobile': (),
        'cancel_attent_time_begin': ('cancel_attent_time', '>='),
        'cancel_attent_time_end': ('cancel_attent_time', '<='),
        'current_attention': (),
        'has_recharge': (),
        'has_partic': (),
        'has_pack': (),
    }

    # 用户ID
    uid = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 电话
    mobile = db.Column(db.String(32), default='', server_default=text("''"))
    # 昵称
    nickname = db.Column(db.String(32), default='', server_default=text("''"))
    # 微信openid
    wx_openid = db.Column(db.String(128), default='', server_default=text("''"))
    # 取消关注时间
    cancel_attent_time = db.Column(db.DateTime())
    # 当前是否关注
    current_attention = db.Column(
        db.Integer, server_default=text("'0'"), default=0)
    # 取关时是否充过值
    has_recharge = db.Column(
        db.Integer, server_default=text("'0'"), default=0)
    # 取关时是否抢过单
    has_partic = db.Column(
        db.Integer, server_default=text("'0'"), default=0)
    # 取关时是否有可用套餐（电话积分）
    has_pack = db.Column(
        db.Integer, server_default=text("'0'"), default=0)

    owner = db.relationship(
        "User",
        primaryjoin="foreign(CancelAttentionUser.uid) == remote(User.id)",
        single_parent=True,
    )

    @property
    def current_attention_display(self):
        return Choice(self.current_attention).display

    @property
    def has_recharge_display(self):
        return Choice(self.has_recharge).display

    @property
    def has_partic_display(self):
        return Choice(self.has_partic).display

    @property
    def has_pack_display(self):
        return Choice(self.has_pack).display


class DemandFinishImg(BaseModel):
    """
    add by zhouhl 2017-09-06
    项目完成图片，用于申报项目完成
    """
    __tablename__ = 'demand_finish_imgs'

    # 用户ID
    user_id = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 项目ID
    demand_id = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 上传图片uri
    img = db.Column(db.String(1024), default='', server_default=text("''"))
    # 审核状态
    state = db.Column(
        db.Integer(),
        default=DemandFinishImgState.UNCHECKED.value,
        server_default=text("'0'")
    )

    owner = db.relationship(
        "User",
        primaryjoin="foreign(DemandFinishImg.user_id) == remote(User.id)",
        single_parent=True,
    )

    own_demand = db.relationship(
        "Demand",
        primaryjoin="foreign(DemandFinishImg.demand_id) == remote(Demand.id)",
        single_parent=True,
        backref=db.backref('finish_imgs', lazy='dynamic'),
    )

    @property
    def img_url(self):
        if self.img:
            host = os.environ.get('img_host')
            return urllib.parse.urljoin(host, self.img)
        return self.img

    @property
    def state_display(self):
        return DemandFinishImgState(self.state).display


class OpcDayStat(BaseModel):
    """
    created by chenhj at 2017/9/12
    日统计数据
    """
    __tablename__ = 'opc_day_stat'

    filter_dict = {
        'start_date': ('stat_date', '>='),
        'end_date': ('stat_date', '<='),
    }

    stat_date = db.Column(db.Date, nullable=False, index=True)
    # 真实订单数
    ro_cnt = db.Column(db.Integer, default=-1)
    # 发布次数
    publish_count = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 抢单数
    bid_cnt = db.Column(db.Integer, default=-1)
    # 抢单人
    bid_people_cnt = db.Column(db.Integer, default=-1)
    # 到期用户数
    expired_people_cnt = db.Column(db.Integer, default=-1)
    # 到期付费人数
    expired_pay_cnt = db.Column(db.Integer, default=-1)
    # 付费人数
    pay_people_cnt = db.Column(db.Integer, default=-1)
    # 付费单数
    pay_cnt = db.Column(db.Integer, default=-1)
    pay_amt = db.Column(
        db.DECIMAL(10, 2), default=0, server_default=text("'0'")
    )
    # 日消耗汇币(抢单)
    day_cost_bid_point = db.Column(db.Integer, default=0)
    # 日消耗汇币(购买派单点)
    day_cost_bid_point_dispatch = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 日消耗汇币(购买铜牌会费)
    day_cost_bid_point_bronze = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 日消耗汇币(其他)
    day_cost_bid_point_other = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 日退返汇币
    day_return_bid_point = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 消耗券额
    voucher_bid_point = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 抵扣券额
    # NOTE 比如抢单花了150汇币，使用了一张200的代金券。则消耗券额为200，抵扣券额为150
    used_voucher_bid_point = db.Column(db.Integer(), default=0, server_default=text("'0'"))

    @classmethod
    def get_ro_cnt(cls, stat_date):
        """
        created by chenhj at 2017/9/15

        """
        from app.war_zone.models import WarZoneDailyStatic

        published_cnt_rec = WarZoneDailyStatic.query.filter_by(
            deleted_at=0,
            date=stat_date
        ).with_entities(
            func.sum(WarZoneDailyStatic.published_count),
        ).first()

        ro_cnt_rec = WarZoneDailyStatic.query.filter_by(
            deleted_at=0,
            date=stat_date
        ).with_entities(
            func.sum(WarZoneDailyStatic.ro_cnt),
        ).first()
        return ro_cnt_rec[0] or 0, published_cnt_rec[0] or 0

    @classmethod
    def get_partic_cnt(cls, stat_date):
        """
        created by chenhj at 2017/9/15

        """
        start_date = stat_date
        end_date = start_date + timedelta(days=1)
        q = Partic.query.filter(
            Partic.deleted_at == 0,
            Partic.status != ParticStatus.DRAFT.value,
            Partic.demand_status.notin_([
                DemandStatus.DISPATCHING.value, DemandStatus.MASTER_DISPATCHING.value
            ]),
            Partic.is_robot.__eq__(False),
            Partic.is_training.__eq__(False),
            Partic.created_at.between(
                start_date.strftime('%s'), end_date.strftime('%s')
            )
        ).join(
            Demand, Demand.id == Partic.demand_id
        ).join(
            User, User.id == Partic.supplier_id
        ).filter(
            User.is_internal.is_(False),
            User.mobile.notin_(MEMBERS_PHONE)
        )
        return q.count(), q.with_entities(Partic.supplier_id.distinct()).count()

    @classmethod
    def get_pay_cnt(cls, stat_date):
        """
        created by chenhj at 2017/9/15

        """
        start_date = stat_date
        end_date = start_date + timedelta(days=1)
        q = Bill.query.filter(
            Bill.deleted_at == 0,
            Bill.type == BillType.INCOMING.value,
            Bill.sub_type.in_([BillSubType.BUY_PACK.value, BillSubType.HALF_PACK.value]),
            Bill.status == BillStatus.PAID.value,
            Bill.created_at.between(
                start_date.strftime('%s'), end_date.strftime('%s')
            )
        ).join(
            User, User.id == Bill.uid
        ).filter(
            User.is_internal.is_(False),
            User.mobile.notin_(MEMBERS_PHONE)
        )
        return q.count(), q.with_entities(
            Bill.uid.distinct()
        ).count(), q.with_entities(
            func.sum(Bill.amt)
        ).first()

    @classmethod
    def get_expired_cnt(cls, stat_date):
        """
        created by chenhj at 2017/9/15

        """
        start_date = stat_date
        end_date = start_date + timedelta(days=1)
        exp_uid_query = Pack.query.filter(
            Pack.deleted_at == 0,
            Pack.out_date == stat_date
        ).with_entities(Pack.uid.distinct()).all()
        exp_uid_list = [x[0] for x in exp_uid_query]
        expired_pay_cnt = 0
        if exp_uid_list:
            expired_pay_cnt = Bill.query.filter(
                Bill.deleted_at == 0,
                Bill.type == BillType.INCOMING.value,
                Bill.sub_type.in_([BillSubType.BUY_PACK.value, BillSubType.HALF_PACK.value]),
                Bill.status == BillStatus.PAID.value,
                Bill.created_at.between(
                    start_date.strftime('%s'), end_date.strftime('%s')
                ),
                Bill.uid.in_(exp_uid_list)
            ).with_entities(Bill.uid.distinct()).count()
        return len(exp_uid_query), expired_pay_cnt

    @classmethod
    def get_day_cost_bid_point(cls, stat_date):
        """
        created by chenhj at 2017/11/8
        取该天消耗的汇币(用得到的 - 返还的)
        """
        start_date = stat_date
        end_date = start_date + timedelta(days=1)
        bid_cost = db.session.query(
            db.func.sum(BidCostRecord.bid_point)
        ).filter(
            BidCostRecord.deleted_at == 0,
            BidCostRecord.cost_type.in_(
                [BidCostType.BID.value, BidCostType.NOT_CONTACT_PUNISH.value]
            ),
            BidCostRecord.created_at.between(
                start_date.strftime('%s'), end_date.strftime('%s')
            ),
        ).join(
            User, User.id == BidCostRecord.user_id
        ).filter(
            User.is_internal.is_(False),
            User.mobile.notin_(MEMBERS_PHONE)
        ).scalar() or 0

        other_cost = db.session.query(
            db.func.sum(BidCostRecord.bid_point)
        ).filter(
            BidCostRecord.deleted_at == 0,
            BidCostRecord.cost_type == BidCostType.LOCKED.value,
            BidCostRecord.created_at.between(
                start_date.strftime('%s'), end_date.strftime('%s')
            ),
        ).join(
            User, User.id == BidCostRecord.user_id
        ).filter(
            User.is_internal.is_(False),
            User.mobile.notin_(MEMBERS_PHONE)
        ).scalar() or 0

        ret_bid_point = db.session.query(
            db.func.sum(BidCostRecord.bid_point)
        ).filter(
            BidCostRecord.deleted_at == 0,
            BidCostRecord.cost_type.in_(
                [BidCostType.NOT_CONTACT_BACK.value, BidCostType.FEED_BACK.value]
            ),
            BidCostRecord.created_at.between(
                start_date.strftime('%s'), end_date.strftime('%s')
            ),
        ).join(
            User, User.id == BidCostRecord.user_id
        ).filter(
            User.is_internal.is_(False),
            User.mobile.notin_(MEMBERS_PHONE)
        ).scalar() or 0
        return bid_cost, 0, 0, other_cost, ret_bid_point

    @classmethod
    def get_voucher_stat(cls, stat_date):
        """
        created by chenhj at 2018/7/6

        """
        return Voucher.query.filter(
            Voucher.status == VoucherStatus.USED.value,
            Voucher.used_date == stat_date
        ).with_entities(
            func.sum(Voucher.bid_point), func.sum(Voucher.used_bid_point)
        ).first()


class UserProtocol(BaseModel):
    """
    created by chenhj at 2017/10/13
    用户协议签订表
    """
    __tablename__ = 'user_protocol'

    filter_dict = {
        'user_id': ()
    }

    search_keys = ('nickname', 'mobile')

    user_id = db.Column(db.ForeignKey('user.id'))
    mobile = db.Column(db.String(16), default='')
    nickname = db.Column(db.String(128), default='')
    version = db.Column(db.String(128), default='')


class BidCostRecord(BaseModel):
    """
    created by chenhj at 2017/10/31
    客户套餐消耗记录
    """
    __tablename__ = 'bid_cost_record'

    user_id = db.Column(db.ForeignKey('user.id'))
    # 消耗类型
    cost_type = db.Column(db.Integer)
    # 当次影响的汇币
    bid_point = db.Column(db.Integer)
    # 扣除后当前剩余汇币
    left_bid_point = db.Column(db.Integer)
    # 抢单, 锁定, 未联系等项目关联
    demand_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 购买派单点, 0表示无限量
    dispatch_point = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 购买新项目提醒, 0表示无限量
    sms_point = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 购买的服务天数
    pack_days = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 转账接收方（或者转账发送方）
    interact_user_id = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 备注信息(utf8mb4_unicode_ci)
    remark = db.Column(
        db.String(64, 'utf8mb4_unicode_ci'),
        default='',
        server_default=(text("''"))
    )

    @property
    def cost_type_display(self):
        """
        消费类型显示
        :return:
        """
        return BidCostType(self.cost_type).display


class IMMessage(BaseModel):
    """
    created by chenhj at 2017/11/22
    im消息记录
    """
    __tablename__ = 'im_messages'

    created_at = db.Column(
        db.BigInteger,
        nullable=False,
        server_default=text("'0'"),
        default=get_timestamp_ms
    )

    # 来自哪个用户id
    im_from = db.Column(db.String(12), default='', index=True)
    # 发给哪个用户id
    im_to = db.Column(db.String(12), default='', index=True)
    # 消息类型
    message_type = db.Column(db.Integer)
    # 消息体
    message = db.Column(db.TEXT, default='')
    # 消息是否已读状态
    read_status = db.Column(db.Boolean(), default=False)
    # im_from与im_to排序后的组合
    group_id = db.Column(db.String(24), default='', index=True)

    @property
    def from_id(self):
        return self.im_from

    @from_id.setter
    def from_id(self, value):
        self.im_from = value
        self.__set_group_id()

    @property
    def to_id(self):
        return self.im_to

    @to_id.setter
    def to_id(self, value):
        self.im_to = value
        self.__set_group_id()

    @property
    def msg_type(self):
        return self.message_type

    @msg_type.setter
    def msg_type(self, value):
        self.message_type = value
        self.__set_group_id()

    def __set_group_id(self):
        self.group_id = '%s:%s:%s' % tuple(
            sorted([str(self.im_from), str(self.im_to), str(self.msg_type)[0]])
        )

    @classmethod
    def un_read_notify_count(cls, user_id):
        """
        create by ld_wu 2017-11-23
        未读消息数量
        :param user_id:
        :return:
        """
        # 使用原生sql增加order by 避免文件排序
        # 优化性能
        count = db.session.execute(
            '''
                SELECT
                    group_id,
                    sum(CASE WHEN
                     (read_status = false AND im_to = :im_to) 
                     THEN 1 ELSE 0 END) AS message_count 
                FROM im_messages 
                WHERE 
                     im_to =:im_to OR im_from = :im_from
                GROUP BY group_id
                ORDER BY NULL
            ''',
            dict(
                im_to=str(user_id),
                im_from=str(user_id)
            )
        ).fetchall()

        return count

    @classmethod
    def newest_message(cls, group_ids):
        """
        create by ld_wu 2017-11-23
        根据group_id来获取最新的消息
        :param group_ids:
        :return:
        """
        subquery = cls.query.filter(
            cls.group_id.in_(group_ids)
        ).group_by(
            cls.group_id
        ).with_entities(
            func.max(cls.id).label('max_id')
        ).subquery()
        return cls.query.join(
            subquery, cls.id == subquery.c.max_id
        ).all()

    @classmethod
    def mark_read_message(cls, group_id, user_id):
        """
        标记某组消息为已读
        :return:
        """
        messages = cls.query.filter(
            cls.group_id == group_id,
            cls.im_to == str(user_id),
            cls.read_status.__eq__(False)
        )
        for each in messages:
            each.read_status = True

    @classmethod
    def message_list(cls, group_id):
        return cls.query.filter(
            cls.group_id == group_id
        ).order_by(
            cls.created_at.desc(),
            cls.id.desc()
        )


class DemandClient(BaseModel):
    """
    created by chenhj at 15/01/2018

    """
    __tablename__ = 'demand_clients'

    search_keys = ('sem_path',)

    filter_dict = {
        'client_source': (),
        'last_visit_time_begin': ('last_visit_time', '>='),
        'last_visit_time_end': ('last_visit_time', '<='),
        'operate_result': (),
    }

    user_id = db.Column(db.ForeignKey('user.id'))
    demand_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 访问我们的次数
    visit_cnt = db.Column(db.Integer, default=0)
    client_source = db.Column(db.Integer)
    last_visit_time = db.Column(db.DateTime)
    # 操作时间(即用户最后一次访问到项目发布的时间, 单位为秒)
    operate_time = db.Column(db.Integer, default=0)
    operator_id = db.Column(db.ForeignKey('admin.id'))
    operate_result = db.Column(db.Integer, default=OperateResult.NA.value)
    # 来源网址(带参数)
    sem_origin_url = db.Column(db.Text, default='')
    # 来源网址(不带参数)
    sem_referer = db.Column(db.String(128), default='')
    # 来源网址参数中的source
    sem_source = db.Column(db.String(64), default='')
    # 主叫号码
    sem_called = db.Column(db.String(64), default='')
    sem_path = db.Column(db.String(64), default='')

    client = db.relationship('User')
    operator = db.relationship('AdminUser')
    demand = db.relationship(
        'Demand',
        primaryjoin='and_(foreign(DemandClient.demand_id) == remote(Demand.id),'
            'Demand.deleted_at==0)',
        single_parent=True,
    )

    @property
    def operate_time_display(self):
        if self.operate_time:
            return '{}分{}秒'.format(self.operate_time // 60, self.operate_time % 60)
        return 0

    @property
    def operate_result_display(self):
        return OperateResult(self.operate_result).display

    @property
    def demand_client_source_display(self):
        return DemandClientSource(self.client_source).display

    @classmethod
    def complete(cls, user_id, operator_id, operate_result, demand_id=0):
        """
        created by chenhj at 17/01/2018

        """
        # 延后发布(已发布)使用demand_id去查询数据, 否则会出现替人发布时找不到数据的情况
        if operate_result == OperateResult.DELAY_PUBLISH_DONE.value and demand_id:
            obj = cls.query.filter(
                cls.demand_id == demand_id,
                cls.operate_result == OperateResult.DELAY_PUBLISH.value
            ).first()
        else:
            obj = cls.query.filter(
                cls.user_id == user_id,
                cls.operate_result.in_(
                    [OperateResult.NA.value, OperateResult.DELAY_OP.value]
                )
            ).first()
        if obj:
            if operator_id:
                obj.operator_id = operator_id
            obj.operate_result = operate_result
            obj.operate_time = (datetime.now() - obj.last_visit_time).seconds
            obj.demand_id = demand_id
            user_id = obj.user_id
        # 若处理结果不是延后处理, 则把其他的草稿订单设置成无效
        if operate_result != OperateResult.DELAY_OP.value:
            invalid_demands = db.session.query(Demand).filter(
                Demand.status.in_([DemandStatus.DRAFT.value, DemandStatus.UNCHECKED.value]),
                Demand.uid == user_id
            ).all()
            for i in invalid_demands:
                i.status = DemandStatus.INVALID.value
                i.admin_remark = '系统自动置为无效订单'

    @classmethod
    def visit(cls, user_id, client_source, **kwargs):
        """
        created by chenhj at 16/01/2018
        用户每访问一次, 都调用下这个方法, 生成或更新一条demand_client的数据
        """
        obj = cls.query.filter(
            cls.user_id == user_id,
            cls.operate_result.in_(
                [OperateResult.NA.value, OperateResult.DELAY_OP.value]
            )
        ).first() or cls.create(
            user_id=user_id
        )
        obj.client_source = client_source
        obj.last_visit_time = datetime.now()
        obj.visit_cnt += 1

        obj.parse_sem_info(**kwargs)

        # 记录日志
        DemandClientLog.create(
            user_id=user_id,
            client_source=client_source,
            last_visit_time=obj.last_visit_time,
            sem_path=obj.sem_path
        )
        return obj

    def parse_sem_info(self, **kwargs):
        """
        created by chenhj at 16/01/2018
        填充sem部分的信息
        """
        if 'enterprise_id' in kwargs and kwargs['enterprise_id']:
            from app.enterprise.models import Enterprise
            ent = Enterprise.get_by_id(kwargs['enterprise_id'])
            if ent:
                self.sem_called = ''
                self.sem_origin_url = ''
                self.sem_referer = ''
                self.sem_source = ''
                # 有很多公司真实公司名写在备注上了
                self.sem_path = '{}-{}'.format(
                    ent.remark or ent.enterprise_name,
                    kwargs.get('enterprise_source', '')
                )
        elif 'sem_called' in kwargs:
            self.sem_called = kwargs['sem_called']
            self.sem_origin_url = ''
            self.sem_referer = ''
            self.sem_source = ''

            dic = {
                SEMGroup.GroupA.value: 'BD+SG+58-A',
                SEMGroup.GroupB.value: 'BD-E',
                SEMGroup.GroupC.value: 'BD-C',
            }
            self.sem_path = dic.get(self.sem_called, '')
        elif 'sem_referer' in kwargs:
            self.sem_referer = urllib.parse.urlparse(kwargs['sem_referer']).netloc
            self.sem_origin_url = kwargs['sem_origin_url']
            if self.sem_origin_url:
                query_str = urllib.parse.urlparse(self.sem_origin_url).query
                parse_qs_dic = urllib.parse.parse_qs(query_str)
                if 'source' in parse_qs_dic and parse_qs_dic['source']:
                    self.sem_source = parse_qs_dic['source'][0].lower()
            else:
                self.sem_source = ''
            self.sem_called = ''

            other_dic = {
                'www.wanpinghui.com': 'A2-PC',
                'm.wanpinghui.com': 'A2-MO',
                't.wanpinghui.com': 'A-PC',
                'mt.wanpinghui.com': 'A-MO',
                'www.wanpingwang.com': 'B-PC',
                'm.wanpingwang.com': 'B-MO',
                'www.maipingba.com': 'C-PC',
                'm.maipingba.com': 'C-MO',
                'www.yongpingba.com': 'D-PC',
                'm.yongpingba.com': 'D-MO',
                'www.dapinghui.com': 'E-PC',
                'm.dapinghui.com': 'E-MO',
                'www.wanpingfa.com': 'F-PC',
                'm.wanpingfa.com': 'F-MO',
            }
            self.sem_path = '{}-{}'.format(
                self.sem_source, other_dic.get(self.sem_referer, '')
            )
        elif 'sender_show' in kwargs:
            sem_dic = {
                '18618207054': 'A-表单',
                '18618226248': 'B-表单',
                '18618226842': 'B-信息流表单',
                '18618221490': 'A-信息流表单'
            }
            self.sem_called = ''
            self.sem_origin_url = ''
            self.sem_referer = ''
            self.sem_source = ''
            self.sem_path = sem_dic[kwargs['sender_show']]
        elif 'sem_path' in kwargs:
            self.sem_called = ''
            self.sem_origin_url = ''
            self.sem_referer = ''
            self.sem_source = ''
            self.sem_path = kwargs['sem_path']
        else:
            self.sem_called = ''
            self.sem_origin_url = ''
            self.sem_referer = ''
            self.sem_source = ''
            self.sem_path = ''


class DemandClientLog(BaseModel):
    """
    created by chenhj at 09/02/2018

    """
    __tablename__ = 'demand_client_log'

    user_id = db.Column(db.Integer)
    client_source = db.Column(db.Integer)
    last_visit_time = db.Column(db.DateTime)
    sem_path = db.Column(db.String(64))


class UserLevelChangeLog(BaseModel):
    """
    add by zhouhl 2018-07-12
    用户等级变更记录
    """
    __tablename__ = 'user_level_change_log'

    filter_dict = {
        'user_id': (),
    }

    user_id = db.Column(db.Integer)
    user_nickname = db.Column(db.String(64))
    user_mobile = db.Column(db.String(64))
    old_level = db.Column(db.Integer)
    new_level = db.Column(db.Integer)
    operator_id = db.Column(db.Integer)
    openator_name = db.Column(db.String(64))

    @property
    def old_level_display(self):
        return UserLevel(self.old_level).display

    @property
    def new_level_display(self):
        return UserLevel(self.new_level).display


class BidUserQueue(BaseModel):
    """
    create by ld_wu 2018-01-24
    中标用户队列
    """

    __tablename__ = 'bid_user_queues'

    # 项目id
    demand_id = db.Column(db.Integer())
    # 冗余中标人手机号
    mobile = db.Column(db.String(20))
    # 中标id
    partic_id = db.Column(db.Integer(), unique=True)
    # 状态 1 待呼叫 2 已呼叫 3 待充值（可转1、4） 4 超时未充值
    status = db.Column(db.Integer(), default=1)
    # 中标用户id
    user_id = db.Column(db.Integer())
    # 项目发布次数
    published_count = db.Column(db.Integer(), default=0)
    # 七陌话单id
    action_id = db.Column(db.String(32), default='', index=True)

    # 项目id与发布次数联合索引
    __table_args__ = (
        db.Index(
            'ix_bid_user_queues_demand_id_publish_count',
            'demand_id',
            'published_count'
        ),
    )


class UserVisitDemand(AbstractModel):
    """
    add by zhouhl 2018-05-04
    用户访问项目详情接口的记录
    """

    __tablename__ = 'user_visit_demand'

    # 用户id
    user_id = db.Column(db.Integer(), primary_key=True, index=True)
    # 项目id
    demand_id = db.Column(db.Integer(), primary_key=True, index=True)
    # 项目发布时间
    demand_published_datetime = db.Column(db.DateTime())
    # 访问渠道，微信公众号 or App
    channel = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 访问次数
    visit_count = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    created_datetime = db.Column(db.DateTime(), default=datetime.now)
    updated_datetime = db.Column(
        db.DateTime(),
        default=datetime.now,
        onupdate=datetime.now,
    )

    user = db.relationship(
        "User",
        primaryjoin="foreign(UserVisitDemand.user_id) == remote(User.id)",
        single_parent=True,
    )

    @classmethod
    def get_or_create(cls, user_id, demand_id, channel):
        record = cls.get_by_args(
            user_id=user_id,
            demand_id=demand_id,
        )
        if not record:
            demand = Demand.get_by_id(demand_id)
            record = cls.create(
                user_id=user_id,
                demand_id=demand_id,
                channel=channel,
                demand_published_datetime=demand.published_datetime,
            )
        return record

    @classmethod
    def visit_counter(cls, user_id, demand_id, channel):
        record = cls.get_or_create(user_id, demand_id, channel)
        record.visit_count += 1

    @property
    def channel_display(self):
        return UserVisitDemandChannel(self.channel).display


class GameExchangeReform(BaseModel):
    """
    add by zhouhl 2018-05-28
    斗地主兑换物品记录
    """
    __tablename__ = 'game_exchange_reforms'

    uid = db.Column(db.Integer())
    pid = db.Column(db.Integer())
    rid = db.Column(db.Integer())
    count = db.Column(db.Integer())
    name = db.Column(db.String(256), default='')
    sign = db.Column(db.String(1024), default='')
    remote_addr = db.Column(db.String(64), default='')
    status = db.Column(db.Integer(), default=0, server_default=text("'0'"))

    @property
    def status_display(self):
        return GameRewardStatus(self.status).display if self.status else '未兑换'

    users = db.relationship(
        'User',
        primaryjoin='foreign(GameExchangeReform.uid) == remote(User.id)',
        single_parent=True,
        backref=db.backref('game_exchange_reforms', lazy='dynamic'),
    )


class Voucher(BaseModel):
    """
    create by ld_wu 2018-07-04
    代金券
    """
    __tablename__ = 'vouchers'

    filter_dict = {
        'grant_date': (),
        'operator_id': (),
        'reason': (),
        'user_id': ()
    }

    # 优惠券的所有者
    user_id = db.Column(db.Integer(), index=True, nullable=False)
    # 汇币数量
    bid_point = db.Column(db.Integer(), default=0)
    # 过期时间
    expired_date = db.Column(db.Date(), index=True)
    # 操作人
    operator_id = db.Column(db.Integer(), default=0)
    # 状态（未使用， 已使用， 已过期）
    status = db.Column(db.SMALLINT(), default=VoucherStatus.AVAILABLE.value)
    # 发放日期
    grant_date = db.Column(db.Date(), default=date.today())
    # 赠送理由
    reason = db.Column(db.SMALLINT(), default=0)
    # 使用时间
    used_date = db.Column(db.Date())
    # 实际使用汇币额
    used_bid_point = db.Column(db.Integer(), default=0)
    # 兑换码(方便统计该兑换码用了多少个)
    redeem_code = db.Column(db.String(32), default='', server_default='')

    gifted_user = db.relationship(
        'User',
        primaryjoin='foreign(Voucher.user_id) == remote(User.id)',
        single_parent=True,
        backref=db.backref('vouchers'),
    )

    operator = db.relationship(
        'AdminUser',
        primaryjoin='foreign(Voucher.operator_id) == remote(AdminUser.id)',
        single_parent=True,
        backref=db.backref('vouchers')
    )

    @property
    def reason_display(self):
        return GiveVoucherReason(self.reason).display

    @property
    def status_display(self):
        return VoucherStatus(self.status).display

    @property
    def is_available(self):
        """
        create by ld_wu 2018-07-04
        :return:
        """
        today = date.today()
        # 过期时间为今天及之前且未使用的券是可用的
        return (
            self.status == VoucherStatus.AVAILABLE.value and
            today <= self.expired_date
        )

    @classmethod
    def gift_voucher(cls, user_id):
        """
        add by zhouhl 2018-07-05
        新用户赠送三张代金券，每张200汇币，30天有效
        """
        today = date.today()
        expired_date = today + timedelta(days=TRIAL_VOUCHER_DAYS)
        for i in range(TRIAL_VOUCHER_CNT):
            Voucher.create(
                user_id=user_id,
                bid_point=TRIAL_VOUCHER_BID_POINT,
                expired_date=expired_date,
                operator_id=0,
                status=VoucherStatus.AVAILABLE.value,
                grant_date=today,
                reason=GiveVoucherReason.SYSTEM_GIFT.value,
            )

    @classmethod
    def max_voucher_bid_point(cls, user_id):
        """
        create by ld_wu 2018-07-05
        返回用户的最大可用的代金券面值
        :param user_id:
        :return:
        """
        today = date.today()
        if not user_id:
            return 0
        voucher = cls.query.filter(
            cls.status == VoucherStatus.AVAILABLE.value,
            cls.expired_date >= today,
            cls.user_id == user_id
        ).order_by(
            cls.bid_point.desc()
        ).first()
        return voucher.bid_point if voucher else 0

    @classmethod
    def available_vouchers(cls, user_id):
        """
        created by chenhj at 2018/7/9

        """
        today = date.today()
        return cls.query.filter(
            cls.user_id == user_id,
            cls.status == VoucherStatus.AVAILABLE.value,
            cls.expired_date >= today
        ).order_by(
            cls.expired_date.desc()
        )

    @classmethod
    def unavailable_vouchers(cls, user_id):
        """
        created by chenhj at 2018/7/9

        """
        return cls.query.filter(
            cls.user_id == user_id,
            cls.status != VoucherStatus.AVAILABLE.value,
        ).order_by(
            cls.expired_date.desc()
        )


class VoucherDayStat(BaseModel):
    """
    create by ld_wu 2018-07-05
    代金券日统计
    """
    __tablename__ = 'voucher_day_stat'

    filter_dict = {
        'start_date': ('stat_date', '>='),
        'end_date': ('stat_date', '<=')
    }

    # 统计日期
    stat_date = db.Column(db.Date(), default=date.today, unique=True)
    # 发放人数
    grant_user_cnt = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 发放张数
    grant_cnt = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 使用人数
    used_user_cnt = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 消耗张数
    used_cnt = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 失效张数
    expired_cnt = db.Column(db.Integer(), default=0, server_default=text("'0'"))


class VoucherDailyStat(AbstractModel):

    __tablename__ = 'voucher_daily_stat'

    filter_dict = {
        'start_date': ('stat_date', '>='),
        'end_date': ('stat_date', '<=')
    }

    id = db.Column(db.Integer, primary_key=True)
    # 统计日期
    stat_date = db.Column(db.Date(), default=date.today)
    # 统计类型
    stat_type = db.Column(db.String(40), default='')
    # 统计值
    stat_cnt = db.Column(db.Integer(), default=0, server_default=text("'0'"))

    __table_args__ = (db.UniqueConstraint('stat_date', 'stat_type'),)

    @classmethod
    def voucher_stat(cls, start_date=None, end_date=None):
        """
        代金券统计信息
        :return:
        """
        stat_dict = defaultdict(dict)
        query = cls.query
        if start_date:
            query = query.filter(
                cls.stat_date >= start_date
            )
        if end_date:
            query = query.filter(
                cls.stat_date <= end_date
            )
        stats = query.all()
        for stat in stats:
            stat_dict[stat.stat_date][stat.stat_type] = stat.stat_cnt

        res = OrderedDict(
            [(stat_date, stat_dict[stat_date])
             for stat_date in sorted(stat_dict.keys(), reverse=True)]
        )

        return res


class StockDayStat(BaseModel):
    """
    created by chenhj at 2018/7/17
    汇币存量统计
    """
    __tablename__ = 'stock_day_stat'

    filter_dict = {
        'start_date': ('stat_date', '>='),
        'end_date': ('stat_date', '<=')
    }

    stat_date = db.Column(db.Date(), default=date.today(), unique=True)
    # 汇币充值
    incoming = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 汇币退返
    ret_bid_point = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 汇币消耗
    cost_bid_point = db.Column(db.Integer(), default=0, server_default=text("'0'"))
    # 今日存量
    stock_bid_point = db.Column(db.Integer(), default=0, server_default=text("'0'"))


class DemandDayStat(AbstractModel):
    """
    create by ld_wu 2018-07-19
    订单分配流程统计
    """
    __tablename__ = 'demand_day_stat'

    filter_dict = {
        'start_date': ('stat_date', '>='),
        'end_date': ('stat_date', '<=')
    }

    id = db.Column(db.Integer(), primary_key=True)
    # 统计日期
    stat_date = db.Column(db.Date(), default=date.today, unique=True)
    # 首发订单数量（首）
    ro_first_cnt = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 优先抢（首）
    bronze_first_cnt = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 竞价（首）
    ongoing_first_cnt = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 回收数量
    renewable_cnt = db.Column(db.Integer(), default=0, server_default=text("'0'"))

    # 优先抢（贰）
    bronze_second_cnt = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 二发订单数量（贰）
    ro_second_cnt = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )
    # 竞价（贰）
    ongoing_second_cnt = db.Column(
        db.Integer(), default=0, server_default=text("'0'")
    )


class BidPointRedPacket(BaseModel):
    """
    add by zhouhl 2018-07-24
    汇币红包
    """
    __tablename__ = 'bid_point_red_packet'

    # 发放红包人ID
    sender_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    # 接收人ID，为0时为群红包，非0时个人红包
    receiver_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    # 发放群红包的IM群ID，空字符串时表示单人红包
    im_group_id = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'), server_default=text("''"), default='')
    # 红包金额
    total_money = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 红包个数
    packet_count = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 红包类型，普通红包还是拼手气红包
    packet_type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 红包备注
    remark = db.Column(
        db.String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    # 红包状态
    status = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 红包抢完时间/过期时间
    overed_at = db.Column(db.Integer, nullable=False, server_default=text("'0'"))

    sender = db.relationship(
        'User',
        primaryjoin='foreign(BidPointRedPacket.sender_id) == remote(User.id)',
        single_parent=True
    )

    @property
    def sender_nickname(self):
        return self.sender.nickname if self.sender else ''

    @property
    def sender_avatar(self):
        return self.sender.avatar_url if self.sender else ''


class GetBidPointRedPacket(BaseModel):
    """
    add by zhouhl 2018-07-24
    汇币红包领取记录
    """
    __tablename__ = 'get_bid_point_red_packet'

    # 对应红包id
    red_packet_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    # 红包类型，普通红包还是拼手气红包(冗余字段)
    packet_type = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 发放红包人ID(冗余字段)
    sender_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    # 领取人ID
    receiver_id = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0, index=True)
    # 领取金额
    money = db.Column(
        db.Integer, nullable=False, server_default=text("'0'"), default=0)
    # 领取时间
    received_at = db.Column(db.Integer, nullable=False, server_default=text("'0'"))
    # 手气最佳
    best_receiver = db.Column(db.Boolean(), default=False, server_default=text('false'))

    red_packet = db.relationship(
        'BidPointRedPacket',
        primaryjoin='foreign(GetBidPointRedPacket.red_packet_id) == remote(BidPointRedPacket.id)',
        single_parent=True,
        backref=db.backref('get_records', lazy='dynamic'),
    )

    receiver = db.relationship(
        'User',
        primaryjoin='foreign(GetBidPointRedPacket.receiver_id) == remote(User.id)',
        single_parent=True
    )

    sender = db.relationship(
        'User',
        primaryjoin='foreign(GetBidPointRedPacket.sender_id) == remote(User.id)',
        single_parent=True
    )

    @property
    def sender_nickname(self):
        return self.sender.nickname if self.sender else ''

    @property
    def receiver_nickname(self):
        return self.receiver.nickname if self.receiver else ''

    @property
    def receiver_avatar(self):
        return self.receiver.avatar_url if self.receiver else ''

    @classmethod
    def get_packet(cls, packet, user):
        """
        add by zhouh 2018-07-24
        领取红包的方法
        根据不同的红包类型，红包数量，写入对应的GetBidPointRedPacket
        :param packet BidPointRedPacket对象
        :param user User对象
        """
        pass


class RedeemCode(BaseModel):
    """
    created by chenhj at 2018/9/25

    """
    __tablename__ = 'redeem_code'

    code = db.Column(db.String(32))
    # 兑换码类型
    redeem_type = db.Column(db.Integer)
    # 兑换物类型
    gift_type = db.Column(db.Integer)
    # 兑换物数量
    count = db.Column(db.Integer)
    # 兑换物有效期
    gift_days = db.Column(db.Integer, default=0, server_default=text("0"))
    # 兑换物面额
    gift_amount = db.Column(db.Integer, default=0, server_default=text("0"))
    # 兑换码有效期
    redeem_expired_date = db.Column(db.Date)
    # 兑换日期
    redeem_date = db.Column(db.Date)
    # 兑换人
    user_id = db.Column(db.Integer)
    # 状态 1 未兑换、2 已兑换、-1 失效
    status = db.Column(db.Integer)

    @property
    def status_display(self):
        return RedeemStatus(self.status).display

    @property
    def redeem_type_display(self):
        return RedeemType(self.redeem_type).display

    @property
    def gift_type_display(self):
        return RedeemGiftType(self.gift_type).display